Why COUNTIF counts wrong (and how to fix it)
=COUNTIF(A2:A100,"*"&D2&"*") returns more than =COUNTIF(A2:A100,D2), some cells have spaces or other text around the value. Clean them with TRIM.Causes and fixes
- Extra spaces or invisible characters
- * or ? in the data acts as a wildcard
- Long numbers are matched on their first 15 digits
- The criteria is longer than 255 characters
- Cells that look empty aren't empty
- You need a case-sensitive count
1Extra spaces or invisible characters
"Paid " with a trailing space isn't counted as "Paid". Data copied from a website or another system often has spaces or non-breaking spaces you can't see.
How to recognise it
=LEN(A2) is larger than the number of characters you see.
Fix
Clean the column with =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in a helper column and paste the results back as values.
2* or ? in the data acts as a wildcard
In COUNTIF, * means "any characters" and ? means "any one character". Counting "A*B" also counts "AxB" and "A-12-B".
How to recognise it
The value you're counting contains * or ?, and the count is too high.
Fix
Put a tilde before the character to count it literally: =COUNTIF(A2:A100,"A~*B"). For a value in a cell, use =COUNTIF(A2:A100,SUBSTITUTE(SUBSTITUTE(D2,"*","~*"),"?","~?")).
3Long numbers are matched on their first 15 digits
COUNTIF treats text that looks like a number as a number, and Excel numbers keep only 15 significant digits. Long IDs such as card or account numbers stored as text can be counted as matches when only the last digits differ.
How to recognise it
The values have 16 or more digits, and COUNTIF finds duplicates that aren't really there.
Fix
Compare the text exactly instead:
=SUMPRODUCT(--(A2:A100=D2))4The criteria is longer than 255 characters
COUNTIF can't match text longer than 255 characters and returns #VALUE!.
Fix
Use the same exact comparison: =SUMPRODUCT(--(A2:A100=D2)).
5Cells that look empty aren't empty
A formula that returns "" looks empty but isn't. COUNTA counts it, so a count of filled cells is too high.
How to recognise it
Click an "empty" cell: the formula bar shows a formula, or a space.
Fix
Count only cells that show something:
=SUMPRODUCT(--(LEN(TRIM(A2:A100))>0))6You need a case-sensitive count
COUNTIF ignores upper and lower case, so "abc" and "ABC" count as the same value.
Fix
Use EXACT, which is case-sensitive:
=SUMPRODUCT(--EXACT(A2:A100,D2))Still stuck?
- To count with an operator and a cell, join them:
=COUNTIF(B2:B100,">"&D2), not">D2". - Numbers stored as text: COUNTIF with
">100"skips text, so convert them with Convert to Number. - COUNTIF counts hidden and filtered rows too.
- For several conditions, use COUNTIFS. All its ranges must be the same size.