Countif not behaving as expected

[cole33]

I have a list of 791 part numbers that are all 16 digits, and I'm trying to identify how many times each number appears in the list. My data is in column A and my formula is in column B. I'm using the following formula and filling it down to the bottom of the list: =COUNTIF($A$1:$A$791,A1). Some cells are returning a value greater than 1 even though they only appear in the list once. The countif formulas for parts 1205087130050100 and 1205087130050101 are both returning a value of 3, but if I delete the first number then the countif formulas change to 0 on the first number and 2 on the second number.

I'd share the sheet but this is my first post here and I'm not sure how to link it. I use this formula frequently and I'm thoroughly stumped as to why it's not working. Any help is greatly appreciated!

 

[tyija1995]

I think if the numbers go above 15 digits Excel rounds them off, so
1205087130050100 AND
1205087130050101
would both be the same as they are 16 digits. So from the 16th digit, the rest of the digits change to 0's.
The 16th digit of those 2 numbers is 0 & 1 respectively, so Excel changes it to 0 & 0 --> the same number. (As the first 15 digits are equal anyway)

 

[shg]

Assuming your part numbers are stored as text (and they must be),

=SUMPRODUCT(--(A1 = $A$1:$A$791))

 

[cole33]

I think if the numbers go above 15 digits Excel rounds them off, so
1205087130050100 AND
1205087130050101
would both be the same as they are 16 digits. So from the 16th digit, the rest of the digits change to 0's.
The 16th digit of those 2 numbers is 0 & 1 respectively, so Excel changes it to 0 & 0 --> the same number. (As the first 15 digits are equal anyway)

Thank you, I suspected that it might be something like that. Any advice on a way to make this formula work with 16 digits?

 

[cole33]

Assuming your part numbers are stored as text (and they must be),

=SUMPRODUCT(--(A1 = $A$1:$A$791))

This worked, thank you shg!

 

[shg]

You're welcome.