The Google Sheets COUNTIF function is used to count items in a range that match a given condition.
For example, consider the following dataset of customer sales:
This formula counts how many times “John” is found in the range:
=COUNTIF(A2:A11,"John")
This one counts how many times “New York” occurs:
=COUNTIF(B2:B11,"New York")
And this one counts how many sales are over $500:
=COUNTIF(C2:C11,">500")
COUNTIF Function Syntax
=COUNTIF(range, criterion)
It takes 2 arguments:
- range
- criterion
The range is the data that you want to count across.
The criterion is the test you want to apply. If you want to apply multiple tests for counting, then you need to use the COUNTIFS function.
Think of the COUNTIF function as the love child of the IF function and COUNT function.
It’s part of the Math family of functions in Google Sheets.
COUNTIF Function Notes
Text Criterion
Text criterion must be enclosed by quotes:
=COUNTIF(B2:B11,"Apartment")
This formula will count all cells that are equal to the word Apartment.
Numeric Criterion
Exact matches with numeric values do not require quotes:
=COUNTIF(A2:A11,500)
This counts all cells with a value of 500.
Conditional Tests
Logical operators are used to create conditional tests:
- > greater than
- >= greater than or equal to
- < less than
- <= less than or equal to
- = equal to
- <> not equal to
They must be enclosed with double quotes:
=COUNTIF(C2:C11,">500")
This counts all cells with values greater than 500.
Case Insensitive
COUNTIF is case insensitive, so these two formulas will produce the same result:
=COUNTIF(E2:E11,"liam williams")
=COUNTIF(E2:E11,"LIAM WILLIAMS")
Counting Blanks and Non Blanks
To count blank cells in a range, use empty double quotes:
=COUNTIF(F2:F11,"")
To count non-blank cells in a range, use the not-equal logical operator “<>“:
=COUNTIF(F2:F11,"<>")
Reference another cell
The criterion for COUNTIF function can be contained in a different cell and referenced by the COUNTIF formula:
=COUNTIF(E2:E11,B15)
If you want to use a logical operator, then it must still be enclosed in double-quotes. Use an ampersand to combine with the reference cell:
=COUNTIF(D2:D11,">"&B15)
Using Wildcards with COUNTIF
COUNTIF Google Sheets supports three wildcards, *, ?, and ~.
The star * matches zero or more characters.
The question mark ? matches exactly one character.
The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards.
Let’s see some examples:
Star wildcard
The star wildcard can go in front of, at the end of, or at both ends of your search string. This formula:
=COUNTIF(B2:B11,"*house*")
will count any values containing house, e.g. house, townhouse, big house, house for sale, etc.
Wildcards can be used with reference cells too:
=COUNTIF(E2:E11,"*"&A15&"*")
Question mark wildcard
This formula:
=COUNTIF(A81:A86,"J?n")
will match any string with 3 characters where the first character is “J” and the last character is “n”, e.g. Jon, Jen, and Jan (as well as JXn, JZn, J7n, etc.).
Tilde Escape wildcard
The Tilde ~ is used when you want to search for the wildcard characters themselves e.g. * and ?
=COUNTIF(G2:G11,"Yes~*")
counts all cells containing Yes*, and
=COUNTIF(G2:G11,"Yes~?")
counts all cells containing Yes?
COUNTIF Template
All of the examples above are available in the following template.
Click here to open a view-only copy >>
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.
You can also read about it in the Google Documentation.
Can the criterion be AND, OR, NOT or IF? Would using those criteria require an array function?
For multiple conditional tests, it’s best to use the COUNTIFS function.
However, as you guessed, it can be achieved as an array formula with COUNTIF however, e.g. this formula counts John or Emily:
=ArrayFormula(SUM(COUNTIF(A2:A11,{"John","Emily"})))
Super helpful, thank you !
You’re welcome! Cheers.
What if a word occurs more than once in a cell? can I count just the cells that have at least 1 instance of the word?
Example: cell has a text string.
Can you return tile once delivered. Any specific way to return product?
I just want the count to be 1 for the word “return”
=IF(DashBoard!F2=””,””,”F = ‘” &DashBoard!F2 & “‘”)
Hello,
I need to count in the query function cells, which contain a certain value only, but I can’t could you please, help?
Thanks
Hi Ben, I’m trying to find a solution for my COUNTIF issue. I would like to count the occassions Roman Numbers occur. So, “I”, “IV”, and so on. The problem is that when I use the EXACT function the COUNTIF will count every “IV” as well when I select “I”. Also I use non capitalized Roman Figures as well (“i” and “I”.
So, how do I use Exact and also limit to the precise text? Untill now I used: SUMPRODUCT(REGEXMATCH(B$2:B, C2)) where B2:B is the range and C2 contains the Roman Number.
Another one: SUMPRODUCT(–ISNUMBER(FIND(C2,B$2:B)))