Skip to content
ArumSoft Learning
KO
    COUNTIFExcel

    Why COUNTIF counts wrong (and how to fix it)

    Works in: All Excel versions, including Microsoft 365, Excel for Mac and Excel for the web · Updated

    Quick answerCheck for extra spaces first. If =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

    1. Extra spaces or invisible characters
    2. * or ? in the data acts as a wildcard
    3. Long numbers are matched on their first 15 digits
    4. The criteria is longer than 255 characters
    5. Cells that look empty aren't empty
    6. 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.