COUNTIF counts the cells in a range that meet one condition. Examples include cells over 20, cells that say "Paid", or names that start with a certain letter. This guide follows Google's COUNTIF help page for Google Sheets. If you use Excel, check Microsoft's COUNTIF page for Excel's own rules, because Excel isn't covered in detail here.
The COUNTIF syntax in Google Sheets
Google's help page gives the syntax as:
COUNTIF(range, criterion)
rangeis the set of cells tested against the criterion.criterionis the condition each cell must meet to be counted.
Count matching cells step by step
- Click an empty cell where you want the count to appear.
- Type
=COUNTIF(and select the range you want to check, for exampleA1:A10. - Type a comma, then your condition in double quotes.
- Close the bracket and press Enter.
Two examples from Google's page:
=COUNTIF(A1:A10,">20")
This counts cells in A1:A10 that hold a number greater than 20. Use the same pattern with other comparison operators, such as "<20".
=COUNTIF(A1:A10,"Paid")
This counts cells that contain the text Paid.
Match partial text with wildcards
Google's page says the criterion can use two wildcards:
?matches any single character.*matches zero or more characters.
So to count every cell that contains "Paid" anywhere in it, such as "Paid late" or "Unpaid", wrap the word in asterisks:
=COUNTIF(A1:A10,"*Paid*")
If you need to match a real question mark or asterisk, put a tilde in front of it: ~? or ~*. Without the tilde, Sheets reads the character as a wildcard and your count will include cells you didn't mean.
Why your COUNTIF returns the wrong number
The text condition isn't in quotes. Google's help page says that if the range contains text to check, the criterion must be a string. Write "Paid", not Paid. The same goes for comparisons: ">20" needs the quotes.
You expected case to matter. COUNTIF isn't case sensitive, per Google. "paid", "Paid" and "PAID" all count the same cells. If you need to tell them apart, COUNTIF alone won't do it.
You tried to give it two conditions. Google's page says COUNTIF handles only a single criterion. For grouped counts across categories, a pivot table in Google Sheets or the QUERY function may suit you better.
A literal * or ? is acting as a wildcard. Add the tilde, as above.
Don't mix it up with the BigQuery version
The same Google help page also describes a separate COUNTIF for BigQuery data in Sheets. Its syntax is COUNTIF(criteria_column, criterion), with a column instead of a range. Google notes that this version doesn't support counting unique rows across multiple columns. If you're working in a normal spreadsheet, ignore the BigQuery section and use the range version.
What about Excel?
This article doesn't confirm Excel's syntax, wildcard rules or limits. Read Microsoft's COUNTIF guide before relying on any of the details above in Excel. Microsoft also has a page on counting numbers greater than or less than a number.
Check it again if your results don't match what's described here.
