COUNTIF counts how many cells in a range match one condition. The condition can be a value, a cell reference, or a piece of text with a comparison in it such as ">3000".
Syntax
COUNTIF(range, criteria)
When to use it
It does more than its name suggests. Counting is the obvious use. But the count itself often matters more than the number. A count of zero means the value is missing, and a count above one means it is repeated. That is how you check whether something exists, find duplicates, or compare two lists.
What trips people up
COUNTIF ignores letter case, so "acme" and "ACME" are counted as the same thing. It does not ignore stray spaces, which is why a value you can clearly see in both lists sometimes refuses to match. TRIM clears that up.
Use wildcards: =COUNTIF(A:A,"*north*") counts every cell with "north" anywhere in it. The asterisk stands for any number of characters.
How do I count everything except one value?
Use "not equal": =COUNTIF(A:A,"<>Closed") counts every cell that does not say Closed.
Why does COUNTIF find duplicates in long ID numbers that are not the same?
COUNTIF reads anything that looks like a number as a number, and Excel keeps only 15 significant digits. Two 16-digit codes that differ only at the end are treated as equal. Adding a wildcard, as in =COUNTIF(A:A,A2&"*"), makes it compare them as text.
Related functions
COUNTIFS — COUNTIFS counts the rows where every condition you give it is true at the same time.
UNIQUE — UNIQUE takes a list and returns it with the repeats removed, one of each value.
SUMIF — SUMIF adds up the numbers in a column, but only on the rows that meet one condition you set.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
