Fix SUM Not Adding Numbers Returned by Nested IF Functions in WPS Spreadsheets
Question details
The user needs SUM to add the numeric results of nested IF formulas in an Excel 2019 Stableford golf scorecard because the formulas currently return text values instead of numbers.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a Stableford golf scorecard and uses nested IF formulas to assign point values before totaling them with SUM.
- Observed behavior
- SUM ignores or omits the nested IF results because quoted numeric constants make the formula return text strings, which SUM does not add.
Open the workbook in WPS Spreadsheets and click a formula cell to check whether the nested IF return values are enclosed in double quotes. Confirm that the result cells use General or Number formatting so numeric outputs display and total correctly.
Remove Double Quotes from Numeric IF Results
Use this when your nested IF formula returns quoted numbers such as "6" or "4" and SUM ignores them.
SUM is designed to ignore text strings, so returning "6" instead of 6 makes the result invisible to SUM. Rewriting the IF formula with unquoted numeric constants creates real numbers that SUM can add.
Click the cell containing the nested IF formula, such as the first Stableford points cell in the scorecard.
In the formula bar, remove the double quotation marks around every numeric result. Change =IF(E7=1,"6",IF(E7=2,"4",0)) to =IF(E7=1,6,IF(E7=2,4,0)).
Press Enter, then drag the fill handle down through the remaining score rows that need the same formula.
Click the SUM cell and confirm that the total now includes all returned point values.
Wrap Existing Text Results with VALUE to Force Numeric Output
Use this if you want a quick correction for existing nested IF formulas that still return text or if you cannot immediately rewrite every formula.
Fix SUM Ignoring Nested IF Results in WPS Spreadsheets
Use WPS Spreadsheets to correct nested IF formulas so they return real numbers instead of text. SUM then calculates the Stableford scorecard total normally while preserving Excel file compatibility.
- 1. Open the workbook: Open the Excel 2019 Stableford scorecard in WPS Spreadsheets.
- 2. Select the nested IF formula cell: Click the first cell that contains the nested IF formula returning the points value.
- 3. Edit the numeric return values: Remove double quotes from numeric results in the formula bar so the formula returns numbers instead of text.
- 4. Fill down and verify: Drag the fill handle down the score rows and check that the SUM cell now adds the returned point values.
- 5. Save the workbook: Save the file in Microsoft Excel .xlsx format to keep the corrected formulas compatible with Excel 2019.
Frequently Asked Questions
Why does SUM ignore the results of my nested IF formula?
SUM ignores text values. If the numeric results in the IF formula are wrapped in double quotation marks, the formula returns text strings such as "6" instead of the number 6, so SUM cannot add them.
How do I make a nested IF formula return a real number?
Remove the double quotation marks from the numeric return values in the formula. For example, use =IF(E7=1,6,IF(E7=2,4,0)) rather than =IF(E7=1,"6",IF(E7=2,"4",0)).
What can I do if my existing formulas already return text results?
Wrap the existing IF formula with the VALUE function, such as =VALUE(IF(E7=1,"6",IF(E7=2,"4",0))), or use Text to Columns on the result column to convert text-formatted numbers to real numbers.
Will this fix work in both WPS Spreadsheets and Microsoft Excel?
Yes. The corrected formulas use standard spreadsheet behavior, so they work in WPS Spreadsheets and Excel 2019 when the file is saved in .xlsx format.
Why does my SUM still not add numbers after removing quotes?
Check for leading apostrophes, hidden spaces, or text-formatted number cells in the result range. You can remove apostrophes, set the cells to Number format, or use Text to Columns to convert the stored text to numbers.
