Fix SUMIFS Returning the Same Result for Different Plot IDs in WPS Spreadsheets
Question details
The user needs to understand why SUMIFS returns the same total for different plot IDs such as 1-1 and 1-23, and how to make identifiers reliable for conditional summing.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is totaling values by plot identifiers in a worksheet and needs SUMIFS to match each plot ID correctly.
- Observed behavior
- SUMIFS returned incorrect or repeated totals for different plot IDs because dash-separated identifiers were being interpreted as dates or inconsistent text values.
Select the plot ID column and check the Number Format box on the Home tab. If the format is General or Date, dash-separated IDs may have been converted to dates instead of remaining text.
Format plot IDs as text before entering them
Use this to prevent WPS Spreadsheets from interpreting dash-separated plot IDs such as 1-1 or 1-23 as dates.
SUMIFS matches criteria against the underlying cell value, so a visible ID of 1-1 can be stored as a date serial number. Formatting the range as Text forces WPS Spreadsheets to keep the identifier exactly as typed.
Select the plot ID column or range that contains identifiers such as 1-1 and 1-23.
On the Home tab, open the Number Format drop-down and choose Text.
Retype the plot IDs, or select Data > Text to Columns > Next > Next > Text > Finish to convert existing cells to text.
Select the SUMIFS formula cell and confirm that each plot ID now returns a distinct total.

Use a consistent non-date identifier format for plot IDs
Use this when entering many plot IDs or reusing the sheet: switch to period notation with leading zeros, such as 1.01 and 1.10.
Stop SUMIFS Mismatches in WPS Spreadsheets with Text Formatting
Format plot ID cells as text in WPS Spreadsheets so SUMIFS reads identifiers exactly as displayed, and keep working with .xlsx workbooks without rebuilding your formulas.
- 1. Open the workbook: Open the affected workbook in WPS Spreadsheets and select the plot ID column.
- 2. Apply the Text number format: Click the Home tab and choose Text from the Number Format drop-down.
- 3. Convert existing entries to text: Retype the plot IDs or use Data > Text to Columns > Text to convert existing cells to literal text.
- 4. Check the SUMIFS result: Select the SUMIFS formula cell and confirm it now returns the correct total for each plot ID.

Frequently Asked Questions
Does filtering a table affect SUMIFS results?
No. Filtering hides rows from view, but SUMIFS still includes all rows in the referenced range. Use SUBTOTAL or AGGREGATE if you need totals based only on visible rows.
What number format should I use for plot IDs like 1-1 and 1-23?
Use Text. Select the cells, go to Home > Number Format > Text, then retype the IDs. You can also prefix an ID with an apostrophe to force text entry.
Why does SUMIFS sometimes treat 1-1 as a date?
With General or Date formatting, spreadsheet applications may interpret dash-separated numbers as dates and store date serial numbers, so SUMIFS criteria no longer match the displayed identifier.
Can I keep dash-separated IDs and still fix SUMIFS?
Yes. Format the plot ID cells as Text before entering values. For cells that already contain converted dates, retype the IDs or use Text to Columns to reapply text format.
Does changing the cell display to Text fix existing date values?
No. Changing only the number format may not convert the underlying stored value. You need to retype the entry or use Data > Text to Columns > Text to convert existing cells.




