When working with large datasets in Google Sheets, such as employee rosters, student attendance records, or monthly sales reports, you often need to quickly count how many times a specific value appears. Manually scanning and counting rows is not only tedious but highly prone to human error.
The solution is the COUNTIF function. This powerful formula allows you to automatically count the number of cells in a specific range that meet a single condition or criteria that you define.
In this guide, you will learn the exact syntax to use the COUNTIF function in Google Sheets, including how to count exact text matches, partial text matches, and numeric values.
The Basic COUNTIF Syntax
The syntax for the COUNTIF function is straightforward. It requires exactly two arguments, separated by a comma:
=COUNTIF(range, criterion)- Range: The group of cells you want Google Sheets to inspect (e.g.,
A2:A100). - Criterion: The condition a cell must meet to be counted (e.g., “Paid”, “>50”, or a cell reference like
C2).
How to Count Exact Text Matches
The most common use case for COUNTIF is tallying specific text strings. For example, imagine you have a list of employee attendance statuses in column B, from cell B2 down to B50. You want to know exactly how many employees were marked as “Absent.”
Click into an empty cell where you want the total to appear and type:
=COUNTIF(B2:B50, "Absent")Important Note: When your criterion is text, you must enclose it in double quotation marks. The formula is not case-sensitive; it will count “Absent”, “absent”, and “ABSENT” equally.
How to Count Numbers Using Logical Operators
COUNTIF is equally powerful when analysing numerical data. You can use standard logical operators (greater than, less than, equal to) to count numbers within a range.
Suppose column C contains the exam scores of 100 students (C2:C101). You need to find out how many students passed the exam by scoring 75 or higher.
Use the following formula:
=COUNTIF(C2:C101, ">=75")Notice that even though 75 is a number, because you are using the greater-than-or-equal-to operator (>=), the entire criterion must be wrapped in quotation marks. Google Sheets will scan the 100 cells and return the exact number of students who achieved 75 or more.
How to Count Partial Text Matches (Using Wildcards)
Sometimes you need to count cells that contain a specific word, even if other text is present in the same cell. You can achieve this using the asterisk (*) wildcard character, which represents any sequence of characters.
For example, if column A contains a list of email addresses, and you want to count how many of them belong to a specific company domain (e.g., @digitash.com), you would use:
=COUNTIF(A2:A100, "*@digitash.com")The asterisk tells Google Sheets: “Count this cell if it contains absolutely anything, as long as it ends with @digitash.com.”
Referencing Another Cell
Instead of hardcoding the criterion directly into the formula, you can reference another cell. This allows you to build dynamic dashboards where changing one cell instantly updates your counts.
=COUNTIF(B2:B50, E1)In this example, Google Sheets will look at whatever text or number is typed into cell E1 and count how many times it appears in the range B2:B50. Notice that when referencing a cell, you do not use quotation marks.
By mastering the COUNTIF function, you can instantly extract meaningful statistics from massive, overwhelming spreadsheets with absolute precision.