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?

Improve this question
2
  • Not sure why this is, but if you type 2014022701000409 into a cell it converts the 9 to a 0
    – CLockeWork
    Jul 16, 2014 at 10:54
  • A, here you go: superuser.com/questions/373997/… The issue is to do with the way Excel handles over-long numbers.
    – CLockeWork
    Jul 16, 2014 at 10:58

1 Answer 1

Reset to default
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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged .