Quick diagnosis: if COUNTIF returns 0, the most likely causes are data type mismatch (reason 1) or extra spaces (reason 4). If it returns #NAME?, your criteria text is missing quotes (reason 2). If it returns #VALUE!, you may be trying to use it across multiple ranges (reason 5).
Data Type Mismatch Between Criteria and Range
The most common cause. If your range contains numbers but your criteria is text "5" (in quotes), or your range contains text "5" but your criteria is the number 5, COUNTIF may return 0 or an incorrect count.
Fix: Make sure the criteria type matches the range type. For numbers in the range: =COUNTIF(A:A, 5) or =COUNTIF(A:A, ">5") — no quotes around the number. For text in the range: =COUNTIF(A:A, "apple"). If your range has numbers stored as text, convert them first using Paste Special → Multiply by 1.
Criteria Text Not in Quotes
Text criteria must be in double quotes. =COUNTIF(A:A, London) will not work — Excel tries to interpret "London" as a named range, fails, and returns 0 or #NAME?.
Fix: Always quote text criteria: =COUNTIF(A:A, "London"). Comparison operators must also be inside quotes: =COUNTIF(A:A, ">100"). When referencing a cell as the criteria, no quotes are needed: =COUNTIF(A:A, B1).
Wildcard Characters Used Incorrectly
COUNTIF supports * (any characters) and ? (one character) as wildcards — but only for text criteria. Common mistakes: using wildcards for number ranges (they only work on text), or forgetting that * matches zero or more characters.
Fix: To count cells containing "London" anywhere: =COUNTIF(A:A, "*London*"). To count cells starting with "Lon": =COUNTIF(A:A, "Lon*"). For number comparisons, use operators instead: =COUNTIF(A:A, ">100"). Wildcards do not work on numbers — use COUNTIFS with multiple criteria for complex number conditions.
Extra Spaces in Data or Criteria
Invisible leading or trailing spaces cause COUNTIF to miss matches. =COUNTIF(A:A, "London") counts 0 if cells in column A contain "London " (with a trailing space) instead of "London".
Fix: Use TRIM to clean data before counting. The cleanest approach: add a helper column with =TRIM(A1) copied down, paste as values over the original, then use that column in your COUNTIF. For a quick one-off check with wildcards: =COUNTIF(A:A, "*London*") — the wildcards absorb leading and trailing spaces.
Using COUNTIF Across Multiple Ranges or Sheets
COUNTIF only works on a single contiguous range on a single sheet. =COUNTIF(A1:A10, B1:B10, "London") is not valid syntax and returns an error.
Fix for multiple ranges on the same sheet: Use COUNTIFS (which handles multiple criteria ranges) or add individual COUNTIFs: =COUNTIF(A1:A10, "London")+COUNTIF(C1:C10, "London").
Fix for cross-sheet counting: COUNTIF cannot span multiple sheets directly. Consolidate the data into one sheet first, or use SUMPRODUCT with INDIRECT — though the latter is complex and slow on large workbooks.
COUNTIF vs COUNTIFS — When to Use Which
Use COUNTIF for a single condition: =COUNTIF(range, criteria)
Use COUNTIFS for multiple conditions: =COUNTIFS(range1, criteria1, range2, criteria2)
COUNTIFS can also replace COUNTIF for a single condition — the syntax is compatible. Many professionals use COUNTIFS exclusively to avoid switching between the two.
Useful COUNTIF Variations
- =COUNTIF(A:A, "London") ← count exact text matches
- =COUNTIF(A:A, "*London*") ← count cells containing "London"
- =COUNTIF(A:A, ">100") ← count numbers greater than 100
- =COUNTIF(A:A, "<>"&"") ← count non-blank cells
- =COUNTIF(A:A, "") ← count blank cells
- =COUNTIF(A:A, B1) ← count cells matching value in B1
- =COUNTIFS(A:A,"X",B:B,">50") ← count rows where A=X AND B>50
Frequently Asked Questions
Is COUNTIF case-sensitive?
No — COUNTIF is not case-sensitive. "London", "LONDON", and "london" are all counted by =COUNTIF(A:A, "London"). If you need case-sensitive counting, use SUMPRODUCT with EXACT: =SUMPRODUCT((EXACT(A1:A100, "London"))*1).
How do I count cells that contain partial text?
Use wildcards: =COUNTIF(A:A, "*partial*"). The * wildcard matches any number of characters before and after "partial", so any cell containing "partial" anywhere in its text will be counted.
COUNTIF is returning the wrong count even though the data looks correct.
Check for invisible trailing spaces with =LEN(A1). A cell containing "London " has length 7 while "London" has length 6. If LEN shows a higher number than expected, use TRIM to clean the data before counting.