COUNTIF counts the cells in a range that meet one condition. This guide covers matching text, numbers and comparisons, using the ? wildcard, and what to do when one condition isn't enough.
The COUNTIF syntax in one line
Microsoft's COUNTIF help page gives the syntax as:
=COUNTIF(range, criteria)
Microsoft also puts it in plain words:
=COUNTIF(Where do you want to look?, What do you want to look for?)
The criteria can be a number, an expression, a cell reference or a text string, according to Microsoft.
Count cells that match a word or another cell
- Click an empty cell where you want the count.
- Type the formula, with the range first and the condition second.
- Press Enter.
To count how many cells in A2:A5 say London, type the text in quotes:
=COUNTIF(A2:A5,"London")
To count cells that match whatever is in another cell, use the cell reference with no quotes:
=COUNTIF(A2:A5,A4)
The cell-reference version is easier to maintain: change A4 and the count updates.
Case doesn't matter. Microsoft's page says COUNTIF ignores upper and lower case in text, so "london" and "LONDON" count the same as "London".
Count numbers above, below or between values
Put a comparison operator inside the quotes with the number. Microsoft's example uses a named range called fruit to count values from 32 up to 85:
=COUNTIF(fruit,">=32")-COUNTIF(fruit,">85")
This subtraction trick is a workaround for the one-condition limit, covered below.
Use ? as a wildcard, and ~ to find a real question mark
A question mark stands in for a single character. Microsoft's example counts entries that are "apple" followed by one variable last letter:
=COUNTIF(A2:A5,"apple?")
The common mistake here is searching for text that contains an actual question mark or asterisk. Excel reads those characters as wildcards, so the count comes back wrong. The fix, per Microsoft, is to put a tilde (~) in front of the character. To count cells that literally contain "apple?":
=COUNTIF(A2:A5,"apple~?")
When to switch to COUNTIFS
COUNTIF uses only a single criterion, Microsoft says.
You have two options:
- Same column, a range of values: subtract one COUNTIF from another, as in the
>=32and>85example above. - Conditions on different columns (for example, region is London and status is Paid): use COUNTIFS. Microsoft describes COUNTIFS as applying criteria across multiple ranges and counting the number of times all the criteria are met.
If you need to add values rather than count them under several conditions, the same idea applies with SUMIFS in Excel.
COUNTIF can't count by cell color
If you've highlighted rows in yellow and want a count of the yellow cells, COUNTIF won't do it. Microsoft's page says COUNTIF can't count cells by background or font color, and the workaround it names is a VBA user-defined function.
A simpler route, if you control the sheet, is to record the reason for the color in a helper column (a word like "Late") and point COUNTIF at that column instead.
Using Google Sheets instead? See COUNTIF in Google Sheets.
