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.
123456789012345111
123456789012345222
123456789012345333
Enter this formula in B1:
=COUNTIF(A1:A3,A1)
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:
=COUNTIF(A1:A3,”123456789012345111″)
However, this array formula works (enter it with Ctrl+Shift+Enter):
=SUM((A1:A3=A1)*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?)
Brett
John, using Excel 2003 SP1, I get the right answer ,1, with all of the examples you gave ??
I get 1 also. And I get zero for SUMIF indicating that Excel is treating it as text. 2003 SP2 (11.8033.8036)
Very strange. I get 3 using Excel 2003 and Excel 2007.
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… ;-)
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.
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
I get 3 on my home PC with Excel 2003 11.8033.8036 SP2.
At home (SP2 11.65606568) I get 3. Strange indeed.
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
I get 3 unless Transition Formula Evaluation is on, then 1.
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].
just checked on the format set to text doesnt change, it stays 3
Lori,
I expected the database functions to behave this way. I’m testing to see if I can paste this link.
http://www.dailydoseofexcel.com/archives/category/worksheet-functions/list-and-table-functions/
But that wildcard thing is, well, wild!! A ? doesn’t work.
Brett
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.
John, I also get the right answer using Excel 2002(10.6501.6626) SP3
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.
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
=COUNTIF(D1:D3,1)
and
=COUNTIF(D1:D3,”1?)
both return 2 while the formulas
=SUMPRODUCT((D1:D3=1))
and
=SUMPRODUCT((D1:D3=”1?))
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
=COUNTIF(A1:A3,A1&”*”)-COUNTIF(A1:A3,A1&”?*”)
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.
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…
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.
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
regards
Eugène
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.
my condition have 19 digit , it error too .
I use data > consolidate to slove my FS problem.
Thanks infos.
This returns 3 as well:
=COUNTIF(A1:A3,CONCATENATE(TEXT(LEFT($A$1,15),”0?),TEXT(RIGHT($A$1,3),”0?)))
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!!!
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):
=COUNTIF(A1:A3,CHAR(173)&A1)
The soft hyphen is the only character that is ignored in text comparisons, so:
=A1=CHAR(173)&A1
always returns TRUE when A1 is text.
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.
Regards
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.