Why Countif give wrong results?

[amzg]

When I use countif for long code return wrong results, is there a limit for criteria or range length?

[BMV]

Thereason is not limit but length. Countif function alwise convert value to decimal an try to find it if it's possible. Only first 15 digits is the same the others replaced to 0 - it is standard for decimal
40742371184342724458440437 -> 40742371184342700000000000
40732971180572724293602175 -> 40732971180572700000000000
40012871875622724335042778 -> 40012871875622700000000000
40952771844352724285954749 -> 40952771844352700000000000
40802371155992724470495873 -> 40802371155992700000000000

Yiu can use =SUMPRODUCT(--($A$2:$A$5000=A2)) be careful $A$2:$A$5000 and not A:A for performane

[avk]

If you try to count very long numbers (16+ digits) in a range with COUNTIF, you may see incorrect results, due to a bug in how certain functions handle long numbers, even when those numbers are stored as text. Consider the screen below. All counts in column D are incorrect —although each number in column B is unique, the count returned by COUNTIF suggests these numbers are duplicates.
This problem is related to how Excel handles numbers. Excel can only handle 15 significant digits, and if you enter a number with more than 15 digits in Excel, you'll see the trailing digits silently converted to zero. The counting problem mentioned above arises from this limit.

Normally, you can avoid this limit by entering long numbers as text, either by starting the number with a single quote ('999999999999999999) or by formatting the cell(s) as Text before entering. As long as you don't need to perform math operations on a number, this is a good solution, and it lets you enter extra long numbers for things like like credit card numbers and serial numbers without losing any numbers.

However, if you try to use COUNTIF to count a number with more than 15 digits (even when stored as text) you may see unreliable results. This happens because COUNTIF internally converts the long value back to a number at some point during processing, triggering the 15 digit limit described above. Without all digits present, some numbers may be counted like duplicates when counted with COUNTIF.

Solution
One solution is to replace the COUNTIF formula with a formula that uses SUM or SUMPRODUCT.

Please Login or Register  to view this content.

[amzg]

Thanks for information