0

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
  • 1,511

1 Answers1

1

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
  • 1,511