Math & Statistical
Count 16+ Digit Card, IBAN & Tracking IDs Safely (Avoid COUNTIF 15-Digit Bug)
Count 16-to-22-digit card tokens, GS1 SSCC barcodes, and bank IDs accurately with SUMPRODUCT(--(A2:A6 = D2)), avoiding Excel COUNTIF's silent 15-significant-digit coercion bug.
Why COUNTIF silently merges distinct 16-digit text IDs in Excel!
IEEE-754 double-precision numbers store at most 15 significant decimal digits—so you rightly store 16-digit payment tokens, 18-digit GS1 SSCC carton codes, and 20-digit SIM ICCIDs as text! Yet in Microsoft Excel, COUNTIF and COUNTIFS automatically coerce numeric-looking text strings into numbers BEFORE comparing them—truncating digit 16+ to zero and falsely treating "4532015112830366" and "4532015112830369" as duplicates!
Two bulletproof defenses prevent false duplicate flags across both Microsoft Excel and Arte Sheets: (1) preserve a non-numeric prefix or hyphen format (such as "TOK-4532015112830366") so coercion never fires, and (2) replace COUNTIF with direct string array equality inside SUMPRODUCT(--(A2:A6 = D2)), which compares text character-by-character to full string length without numeric truncation!
| Long Identifier Text (A) | Last Digit | Direct Equality --(A2:A6 = D2) | Excel COUNTIF Trap on Pure Digits |
|---|---|---|---|
| TOK-4532015112830366 | ...366 | 1 (Exact text match) | Safe with prefix; pure 16 digits collide in Excel COUNTIF! |
| TOK-4532015112830369 | ...369 | 0 (Distinct 16th digit!) | SUMPRODUCT(--(A2:A6=D2)) NEVER truncates digit 16 |
| TOK-4532015112830366 | ...366 | 1 (Second true occurrence) | Exact duplicate count = 2 |
| TOK-4532015112830360 | ...360 | 0 (Distinct 16th digit!) | Zero false-positive matches |
Full-Precision Long-String Count with SUMPRODUCT(--(Range = Target))
Compares every character of A2:A5 directly against target ID D2 without numeric 15-digit coercion, returning 0 when D2 is blank.
=IF(D2 = "", 0, SUMPRODUCT(--(A2:A5 = D2)))Duplicate Audit Flag for Long Account or Carton Identifiers
Flags whether the queried long identifier appears more than once in A2:A5.
=IF(SUMPRODUCT(--(A2:A5 = D2)) > 1, "Duplicate Long ID", "Unique or Not Found")Try the example
Use the original values or change them to test your understanding.