Fix the COUNTIF error

What COUNTIF means

COUNTIF 'not working' usually means it returns 0 when matches are expected, returns a wrong count, or shows a #VALUE! error. The formula itself is rarely broken — the problem is almost always a mismatch between how data is stored and how the criteria is written.

Common causes

• Comparison operators written without quotes — =COUNTIF(A:A,>5) is a syntax error or evaluates incorrectly; the correct form is =COUNTIF(A:A,">5").
• The criteria is case-sensitive but COUNTIF is case-insensitive by design — COUNTIF(A:A,"Apple") and COUNTIF(A:A,"apple") return the same result, which may not be the expected behaviour.
• The range includes cells with mixed data types (some text, some numbers) and the criteria only matches one type.
• Wildcard characters (* or ?) appear in the data literally but COUNTIF treats them as wildcards, causing over-counting.
• The range spans entire columns (A:A) but the data is on a different sheet or the reference is accidentally pointing to the wrong column.

Example fix

The comparison operator must be inside a quoted string. Without quotes, the formula either errors or compares incorrectly depending on the spreadsheet version.

How to fix it

1. 1Quote all comparison operators: =COUNTIF(A:A,">5") or =COUNTIF(A:A,"<>0"). Use & to combine with a cell reference: =COUNTIF(A:A,">"&B2).
2. 2Accept that COUNTIF is case-insensitive. For case-sensitive counting, use SUMPRODUCT: =SUMPRODUCT((EXACT(A2:A100,"Apple"))*1).
3. 3Audit the range for mixed types with =ISNUMBER(A2) in a helper column. Separate numeric and text counting, or standardise the data.
4. 4To count literal asterisks or question marks, escape them with a tilde: =COUNTIF(A:A,"~*") counts cells that literally contain *.
5. 5Double-check the range reference by clicking the formula cell and observing the coloured range highlight, or type the range manually rather than clicking.

Frequently asked