WPS Spreadsheet: SUM Function vs. Plus Operator for Text Values
Question details
The user needs to understand why the plus operator and SUM function produce different results when referenced cells contain numbers stored as text.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating totals from cells that appear to contain numbers but are actually stored as text.
- Observed behavior
- The plus operator converts numeric text such as "10" and "20" and returns 30, while SUM may ignore those text entries and return 0.
Select each source cell and check the formula bar or Number Format setting to confirm whether the values are stored as text. Identify whether the text contains extra spaces, currency symbols, or other characters that could prevent conversion.
Understand the difference between the plus operator and SUM
Use this explanation when you need to determine why two formulas return different totals for text-formatted numbers.
The plus operator evaluates each referenced value as part of an arithmetic expression and attempts to convert numeric text into numbers. SUM generally ignores text values in referenced cells, so a range containing only text-formatted numbers can produce 0.
In an empty cell, enter =A1+B1 and press Enter. If A1 and B1 contain the text values "10" and "20", WPS Spreadsheet converts them and displays 30.
In another empty cell, enter =SUM(A1:B1) and press Enter. Because the referenced values are text, SUM can ignore them and display 0.
Convert text values before using SUM
Use this solution when you need a reliable total from cells whose numeric content is stored as text.
Calculate Text-Stored Numbers Correctly in WPS Spreadsheet
WPS Spreadsheet lets you diagnose text-formatted numbers, compare SUM with the plus operator, convert values, and build formulas with VALUE for accurate totals.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing the text-formatted values.
- 2. Inspect the source cells: Select the cells and review the formula bar or Home > Number Format to determine whether the values are text.
- 3. Convert or calculate: Convert the cells to Number format, or enter =SUM(VALUE(A1),VALUE(B1)) in a blank cell to calculate without changing the source data.
- 4. Confirm the total: Press Enter and verify that the displayed total matches the numeric values in the source cells.
Frequently Asked Questions
Why does =A1+B1 return 30 while =SUM(A1:B1) returns 0?
The plus operator attempts to convert numeric text into numbers during arithmetic. SUM generally ignores text in referenced cells, so it may return 0 when A1 and B1 contain text such as "10" and "20".
How can I make SUM include numbers stored as text?
Convert the source cells to real numbers, or use =SUM(VALUE(A1),VALUE(B1)) to convert the text values inside the formula before adding them.
What is the easiest way to check whether a cell contains text or a number?
Select the cell and inspect the formula bar and Home > Number Format. You can also test it with an arithmetic formula such as =A1+0; if the value is numeric text, the formula typically converts it.
Will VALUE work if the cell contains spaces or currency symbols?
VALUE works with recognizable numeric text, but extra spaces or unsupported symbols can cause an error. Clean the text first, for example with TRIM, SUBSTITUTE, or manual editing, and then apply VALUE.
