Countif incorrectly matches long number

In Excel 2013 or 2010, if you put this value in column B1:

2014022701000400

And then this formula in column C1:

=COUNTIF(B1,"2014022701000409")

The formula will evaluate to 1. But the numbers are different: the one in the formula ends with a 9, the one in the cell ends with a zero.

Why does countif match a value it shouldn't? Is there a workaround available?

Andomar

Posted 2014-07-16T10:30:18.320

Reputation: 1 221

Not sure why this is, but if you type 2014022701000409 into a cell it converts the 9 to a 0 – CLockeWork – 2014-07-16T10:54:49.770

A, here you go: http://superuser.com/questions/373997/adding-more-than-15-digits-in-excel The issue is to do with the way Excel handles over-long numbers.

– CLockeWork – 2014-07-16T10:58:27.100

Answers

Like CLockeWork comments it appears to be an issue with long numbers. The argument to countif is treated like a number even if it is too long.

You can force a cell to be text-valued with a ' character, like '2014022701000400. However that does not work for formula arguments. The second argument to countif does not allow a ' to force it to be text; it's always interpreted as a number.

One solution is to add an in-between column that prefixes an a:

2014022701000400
="a" & B1
=COUNTIF(C1,"a2014022701000409")

That works, but it ain't pretty.

Andomar

Posted 2014-07-16T10:30:18.320

Reputation: 1 221