COUNTIF not working: causes and fixes

Cause 1: you need a partial match

COUNTIF compares the whole cell with the criteria. A support log where products are called "Router X2" and "Router X4" has no cell that equals "Router", so =COUNTIF(A2:A7,"Router") returns 0.

Add wildcards. * stands for any number of characters, ? for exactly one. "Router*" means starts with Router; "*Kit*" means contains Kit anywhere. With a cell, join the wildcards on: =COUNTIF(A2:A7,"*"&G2&"*").

Cause 2: hidden spaces

A3 holds "Router X2 " with a trailing space, so =COUNTIF(A2:A7,"Router X2") returns 1 instead of 2. =LEN(A3) gives 10 for a 9-character name.

The proper fix is to clean the column with TRIM (helper column, fill down, paste as values). If you cannot touch the data, SUMPRODUCT can trim each cell while it counts. Avoid fixing this with "Router X2*": it would also count a future "Router X2 Pro".

Cause 3: your data contains * or ?

Because * and ? are wildcards, COUNTIF reads them as patterns even when you mean the characters. UK exam grades include A*, and =COUNTIF(E2:E7,"A*") counts everything that starts with A, giving 4 instead of 2.

Put a tilde (~) in front of a wildcard character to make it literal: "A~*" means the text A*. Use "~?" for a real question mark and "~~" for a real tilde. To count exactly "A" and nothing else, "A" on its own already works.

Cause 4: long numbers and numbers stored as text

COUNTIF turns anything that looks like a number into a number before comparing. Excel only keeps 15 significant digits, so 19- and 20-digit values like SIM card numbers, card numbers or tracking codes are compared on their first 15 digits. C2, C3 and C4 differ only in the last digits, and COUNTIF says each appears 3 times. A duplicate check built on it flags every one.

The same conversion means COUNTIF counts the text "1001" and the number 1001 as equal. That helps with mixed data, but comparison criteria like ">100" only see real numbers, so convert text numbers before counting ranges. For long IDs, compare directly with SUMPRODUCT, which keeps them as text. Another common workaround is =COUNTIF(C2:C7,C2&"*"), which forces a text comparison but also counts longer IDs that start with the same digits.

Cause 5: criteria longer than 255 characters

COUNTIF and the other IF-family functions (SUMIF, COUNTIFS, AVERAGEIF) cannot match criteria longer than 255 characters. Microsoft documents that the result is wrong; in practice you usually see #VALUE!. It shows up when counting long product descriptions, survey comments or URLs.

Use SUMPRODUCT with a direct comparison, which has no such limit: =SUMPRODUCT(--(D2:D500=G2)). Add TRIM inside if the text was pasted from elsewhere.

1. Check the length of the criteria with =LEN(G2). Over 255 means COUNTIF will not work.
2. Replace =COUNTIF(D2:D500,G2) with =SUMPRODUCT(--(D2:D500=G2)).
3. For a contains-style match on long text, use =SUMPRODUCT(--ISNUMBER(SEARCH(G2,D2:D500))).

Other reasons COUNTIF gives the wrong number

Operators must be in quotes and joined to cells with &: ">"&F2, not ">F2" (which counts cells greater than the text F2). COUNTIF is not case-sensitive, so "Open" also counts "open": =COUNTIF(B2:B7,"Open") returns 3 here, including the lowercase B5.

COUNTIF pointing at another workbook returns #VALUE! when that workbook is closed. And a range that slides when you copy the formula down drops rows; lock it with $A$2:$A$7.