BUG in Excel CountIF function

5
votes

I am having problems with the CountIf Function in Excel.

=COUNTIF(A:A,A2)

The A column consists of these items:

0107791489614255200011140926107503100513

0107791489614255200011140926107503100457

0107791489614255200011140926107503100518

0107791489614255200011140926107503100503

0107791489614255200011140926107503100519

0107791489614255200011140926107503100444

0107791489614255200011140926107503100521

0107791489614255200011140926107503100438

0107791489614255200011140926107503100449

0107791489614255200011140926107503100443

0107791489614255200011140926107503100501

0107791489614255200011140926107503100455

the formula results to 12, even though these set of strings are not really the same at all. It counts these strings as similar strings, I am thinking this is related to its string length?

What do you guys think? I appreciate your help.

2
excelformulacountif
It's version 14.0.7128.5000 – Synectouche
I would highly recommend not to use such IDs which contain only numeric digits. As you have seen there are problems with them because often they get into numeric context when they should not. This is not only within calculations but also within data transfer. If they would start with a letter, then those problems would not occur. – Axel Richter

2 Answers

5
votes

+1, A good question. Not really a bug but a feature!

This is due to Excel implicitly converting the inputs to its internal numeric type and losing precision in doing so. Excel's internal numeric type is an IEEE floating point double precision number. (Although it does clever things with formatting and error propagation so it appears to get sums like 1/3 + 1/3 + 1/3 correct).

As they are so similar they all compare as mutually equal.

One remedy would be to prefix each string with ' (single quotation) which will prevent the conversion to the numeric type. Then the COUNTIF value returns 1. (At least in my version of Excel; 2013).

4
votes

Preceding the strings with a single apostrophe will not remedy the situation. COUNTIF is designed to interpret data as numerical, where possible, irrespective of the datatype of the values in question. This is sometimes helpful, sometimes (as here) not.

SUMPRODUCT does not exhibit this property:

=SUMPRODUCT(0+($A$1:$A$12=A2))

will return 1, as desired.

Regards