Function Problems

Fix SUMIF Treating Similar 16-Digit Text Values as the Same

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 868 views

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.

Fix SUMIF Treating Similar 16-Digit Text Values as the Same
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.
Before you start

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.

Solution 1Recommended

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.

1
Open your WPS workbook and select the result cell

Click the cell where you want the total or count to appear in WPS Spreadsheets.

2
Enter the SUMPRODUCT formula

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.

3
Press Enter and verify the result

Press Enter and cross-check a few rows to confirm that only exact 16-digit text matches are included.

Use SUMPRODUCT for exact text comparison of 16-digit identifiers
Exact text totals: This formula sums only rows where the identifier cell exactly matches the criteria text, avoiding the 15-digit rounding behavior.

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. 1. Open the affected workbook in WPS Spreadsheets: Launch WPS Office and open the workbook containing the 16-digit identifiers.
  2. 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. 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. 4. Adjust the formula ranges: Replace the example ranges with the actual identifier range, criteria cell, and sum range in your workbook.
  5. 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.
Free WPS Spreadsheets supports SUMPRODUCT and exact text comparison for long identifiersOpens and saves .xlsx, .xls, and .csv files with Microsoft Excel formatting compatibilityFamiliar ribbon and formula engine make Excel workbooks easy to edit without retrainingLightweight software runs on Windows, macOS, Android, and iOS
microsoft office alternative - wps office

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.