Problem with COUNTIF formula

When trying to answer a thread I came across this situation when trying to use COUNTIF to reveal duplicates.

The OP has an 18 digit serial number stored as text, I thought

=COUNTIF($A1:$A$1,$A2)

Dragged Down
would show duplicates if the returned value was greater than 0, the result was not at all what I was expecting. (See the attached workbook)

Step 2 I tried MATCH as a check and this does return what I was expecting.

Step 3 was to convert the text string to a number and run the COUNTIF again, still seems wrong

Step 4 concatenated the string with a trailing "A" and the COUNTIF now returns the expected result

Why does the original formula apparently not work?....


Slainte
Alistair