Most users know that Excel stores no more than 15-digits of a value. Therefore, if you enter items such as credit card numbers, you must format the cell as Text, or precede the number with an apostrophe.

Try this. Enter the following 18-digit strings into A1:A3. They differ only in the last three digits. Precede each with an apostrophe, or Excel will replace the last three digits with zeros.


Enter this formula in B1:


The formula counts the number of entries that are the same as cell A1. It should return 1, but it actually returns 3. COUNTIF is ignoring the last three characters of the strings. You get the same result if you format the cells as Text, and even this formula returns the wrong answer:


However, this array formula works (enter it with Ctrl+Shift+Enter):


Posted in Uncategorized

30 thoughts on “COUNTIF Bug

  1. John,

    It looks like SUMIF has the same condition. An array fixes that too. The Database functions Dsum, Dcount, etc, default to a “starts with” condition anyway, but can be fixed with a boolean gate in the criteria section. That tip I picked up here. Thanks for the info about Sumif! (Fixed in next Excel release?)


  2. I get 3 with XL2002 SP3 (10.6501.6735). I think that the moral of the story is that CountIf and SumIf are kinda buggy sometimes. So if you don’t always need the right answer then feel free to use it with your Credit Card Numbers… ;-)

  3. Using Excel 2002, I get 3. But if I use a non-numeric character(s) in the string (e.g. 123456789012345a111), I get the correct answer of 1. However the regular IF function works like it should. The formula =IF(A1=A2,1,0) evaluates to 0. It seems quirky.

  4. Very strange indeed; I have the same version as Dick [SP2 11.8033.8036) and it returns 3 for all examples.

    Maybe it’s related to the guy that wrote my PUP add-in

  5. got 3 too on 2002 and 2003, maybe the one that got 1 had the cell format set to text, I think it works too

  6. Also 3 and get the same behavior for database functions and Advanced filter, but adding a wildcard * to the criteria returns 1 in all cases – v.odd [10.6501.6626].

  7. Another little known feature of “countif” is that it fails to work at all on strings longer than 255 characters (Excel2k) – this is one of a several Excel functions that were never fully updated when the maximum content of a cell was increased from 255 to 32k characters.

  8. 1. Countif() has a mind of its own amidst many other things.

    2. May force the numeric string to text by, =”123456789012345111? ; and yet, Countif takes that to be a number still, 1.23456789^+17 (Excel-2000, server version).

    3. At long last, the numeric string may be cast in the stone of text by,
    =Char(28 to 31)&”123456789012345111? or =Char(160)&”123456789012345111? (amongst other possibilities ?!).

    4. Regards.

  9. I get 3 using Excel 2003 SP1.

    This is just how COUNTIF works. It’s second argument is ALWAYS treated as a string, even when it’s entered as a number. That’s why it didn’t matter whether its second argument was a reference to A1 or the text value of A1.

    This functionality leads to ambiguity. When COUNTIF’s second argument is a number, it matches both numbers AND text strings in its first argument range that have the same text representation. Enter 1 as a number in D1, =”1? in D2 and leave D3 blank. The formulas




    both return 2 while the formulas




    both return 1. Since =1=”1? returns FALSE, it’s COUNTIF that provides unusual functionality.

    With the original 18-digit strings in A1:A3, COUNTIF’s second argument is interpretted as a string, but since that argument looks like a number, COUNTIF proceeds to interpret it as a number, and that’s the point at which it’s munged into 15 decimal digits of precision. It does the same thing for the values in A1:A3, converting them to numbers, thus ignoring their last 3 digits.

    There’s an imperfect work-around: force Excel to treat these values as text by using


    which forces COUNTIF to treat all cells in A1:A3 as text, and the second COUNTIF call reduces the count for any cells in A2:A3 that contained A1 as a proper leading substring.

  10. It seems that excel converts text strings that contain only numbers into number format before doing any calculations, maybe in order to lower its memory usage…

  11. Gianni –

    “maybe in order to lower its memory usage”

    More likely an assumption that if something in a spreadsheet looks like a number, you want to treat it in a calculation as a number.

  12. Hi,
    referring to »countif bug»
    Enter this formula in B1:

    =COUNTIF(A1:A3,A1) I agree returns 3 ?? why don’t know
    but if countif(a1:A1,A3) you get »your» 1
    see you online

  13. Excel is a good tool for performing basic addition and subtraction, and occasionally multiplication and division. For complex engineering problems, cancer research, and science problems, I would not use Excel. The results are simply untrustworthy and highly unstable.

  14. This returns 3 as well:

  15. If you want to add to the confusion try this similar RIGHT bug…
    type “‘1234567890123456789? in A1, then run these formulas against it:
    =RIGHT(A1,10) and =TEXT(RIGHT(A1,10)
    I get “0123456789? for the =RIGHT formula and
    I get “123456789? for the =TEXT(RIGHT) formula

    To add some more confusion, add a “0? to the end of the text in A1 and check the answers; they match
    Keep going – add a “1? to the end of the text and they still match!
    Keep going – add “2?-“9? and when you reach “9?, they don’t match again!

    Those darn 9’s!!!

  16. Pingback: COUNTIF issue
  17. Eleven years on, i just stumbled upon a reliable way to get COUNTIF[S] to deal with text numbers which i’m posting here for reference.

    The solution is to prefix the criteria string with a soft hyphen character (Alt+0173):


    The soft hyphen is the only character that is ignored in text comparisons, so:


    always returns TRUE when A1 is text.

  18. Another fantastic (and random!) discovery, Lori. Just stumbled upon this after wondering if anybody had managed to come up with a decent solution to this issue.


  19. The problem occurs in the Excel 2016 version of conditional formatting to highlight duplicates. Did okay with most, but counts some others as dupes based only on the first 15 characters.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.