How do I fix this error (excel thinks two different values are the same)

[arielks]

This is messing my entire spreadsheet up.

I have two invoice numbers:
1: 4189032830052819
2: 4189032830052818

they differ by one number (representing the year but that's irrelevant)

if I go:
Countif(A1:A2,A1)
the result I get is 2, even though A1 only exists once.

similarly if I do conditional formatting to show duplicates, excel thinks these two are duplicates and highlights them.

How do i get around that? (I can't change these Invoice numbers, they have to stay the same for consistency with other functionalities of the spreadsheet)

 

[Eric W]

This just showed up yesterday in a thread. It appears there is a bug in Excel where COUNTIF incorrectly matches numbers saved as text longer than a certain number of digits. You can get around it with something like:

=SUMPRODUCT(--(A1:A2=A1))

 

[arielks]

is there a similar solution for Maxifs? (Maxifs seems to suffer from the same problem.)

 

[Eric W]

Try the array version:

=MAX(IF(A1:A3=A1,B1:B3))

confirmed with Control+Shift+Enter.