Bug countif text

Question

Hi,

I think I found a bug in Excel. See below. The formula in column B is countif(A:A,A2) and results in 5 which is incorrect as you can see in the last character. Note that column A are not numbers but text! Looks like Excel converts this into a number before counting.

a	b
004068110005079077	5
004068110005079072	5
004068110005079075	5
004068110005079074	5
004068110005079076	5

Answer 1

I don't know how the algorithm works or why, but COUNTIF, SUMIF etc. clearly try to treat text and numbers interchangeably.

=COUNTIF(range, 5) returns the same result as =COUNTIF(range, "5")

Answer 2

thanks for the workaround, but it is a workaround...

does this function treat them as numbers because of performance?

Answer 3

You've run into an infuriating feature of Excel: even though the values in column A are text values, COUNTIF insists on treating them as numbers. But since the values are too large for it to handle, it truncates the last digit to 0. So it sees all values as 4068110005079070.

As a workaround, you can use

=SOMPRODUCT(--(A2:A6=A2))

Answer 4

COUNTIF has always worked that way. You should use SUMPRODUCT if you have long numeric text.