Fix SUMIF Treating Similar 16-Digit Text Values as the Same
Question details
User needs to calculate totals or counts based on 16-digit numeric-looking text identifiers without SUMIF or COUNTIF merging distinct values because of the 15-digit precision limit.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is aggregating or counting records by identifiers such as 16-digit account, card, or serial numbers that are stored as text.
- Observed behavior
- SUMIF and COUNTIF treat different 16-digit text values as the same criteria match, producing incorrect totals or counts.
Confirm that the identifier column is stored as text, not as numbers. When entering 16-digit values, format the cells as Text first or type a leading apostrophe so the values are not coerced to numbers.
Use SUMPRODUCT for exact text comparison of 16-digit identifiers
Use this formula when you need an exact text match for long numeric-looking IDs; SUMPRODUCT avoids the numeric coercion that affects SUMIF and COUNTIF.
SUMIF and COUNTIF can interpret long text values as numbers and lose precision after 15 significant digits. SUMPRODUCT with a direct equality comparison keeps text values as text, so only identical strings match.
Click the cell where you want the total or count to appear in WPS Spreadsheets.
Type =SUMPRODUCT(($A$2:$A$100=E2)*$B$2:$B$100) and replace $A$2:$A$100 with your identifier range, E2 with the criteria cell, and $B$2:$B$100 with the values to sum.
Press Enter and cross-check a few rows to confirm that only exact 16-digit text matches are included.

Store long identifiers as text before entering data
Use this approach before data entry to prevent WPS Spreadsheets or Excel from converting long ID values into rounded numbers.
Fix long-ID SUMIF mismatches in WPS Spreadsheets with exact text formulas
Use WPS Spreadsheets to keep 16-digit identifiers as text and replace SUMIF or COUNTIF with SUMPRODUCT for exact text matching.
- 1. Open the affected workbook in WPS Spreadsheets: Launch WPS Office and open the workbook containing the 16-digit identifiers.
- 2. Confirm the identifier column is formatted as Text: Select the identifier column, open the Home tab, and verify or apply the Text number format.
- 3. Click the result cell and insert the SUMPRODUCT formula: Click the cell where the correct total or count should appear and enter =SUMPRODUCT(($A$2:$A$100=E2)*$B$2:$B$100).
- 4. Adjust the formula ranges: Replace the example ranges with the actual identifier range, criteria cell, and sum range in your workbook.
- 5. Press Enter and save the workbook: Press Enter to calculate the exact result, then save the file as .xlsx or another Excel-compatible format.

Frequently Asked Questions
Why does SUMIF treat different 16-digit text values as the same?
SUMIF and COUNTIF internally compare values numerically. After 15 significant digits, unique identifiers can be rounded to the same number even if they are stored as text.
Which formula should I use instead of SUMIF for 16-digit text criteria?
Use SUMPRODUCT with an equality comparison, such as =SUMPRODUCT(($A$2:$A$100=E2)*$B$2:$B$100), to force exact text matching.
Does formatting cells as Text fix already rounded 16-digit numbers?
No. Formatting as Text after entry does not restore lost digits. You must re-enter the original identifiers as text, ideally with a leading apostrophe.
Can I use COUNTIF for 16-digit text values?
It is not recommended. COUNTIF can have the same precision issue. Use SUMPRODUCT with an equality expression such as =SUMPRODUCT(--($A$2:$A$100=E2)) for exact counting.
Is this issue specific to Microsoft Excel?
The limitation comes from the shared 15-digit numeric precision behavior in spreadsheet engines, including Excel and WPS Spreadsheets. The SUMPRODUCT text-comparison workaround applies to both.




