Fix Excel SUM Formulas That Reference Numbers Stored as Text
Question details
The user needs an Excel SUM formula to include numeric values returned by other formulas as text.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A SUM formula references cells whose formulas return quoted numbers instead of numeric values.
- Observed behavior
- SUM ignores the text values in the referenced cells, so the total does not include them.
Select a referenced cell and inspect its formula in the formula bar. Look for numbers enclosed in quotation marks, such as "5", which make the formula return text.
Return a Number From the Source Formula
Use this method when you can edit the formulas that produce the values being summed.
Select a cell referenced by SUM and check the formula bar for a quoted number, such as =IF(F28=0,"5","").
In the formula bar, change the formula to =IF(F28=0,5,"") and press Enter. Leave the empty-string quotation marks in place if the false result should remain blank.
Apply the change to any other affected source formulas, then select the SUM cell and verify that its total includes the numeric results.

Convert Formula Results in a Helper Column
Use a helper column when you cannot change the original formulas but their text results represent numbers.
Fix Text Numbers in WPS Spreadsheets
Open the workbook in WPS Spreadsheets and edit the source IF formulas to return numbers that SUM can include.
- 1. Open the workbook: In WPS Spreadsheets, select Open and choose the Excel workbook.
- 2. Find the source formula: Select a cell referenced by the SUM formula and inspect its formula in the formula bar.
- 3. Return a numeric value: Change a quoted number to a number without quotation marks, for example =IF(F28=0,5,""), and press Enter.
- 4. Check and save: Check the SUM cell, then use File > Save to save the workbook.

Frequently Asked Questions
Why does SUM ignore a number returned by an IF formula?
If the IF formula returns a quoted value such as "5", the result is text. SUM ignores text in referenced cells. Change "5" to 5 in the source formula to return a number.
Can I leave the false result of my IF formula blank?
Yes. Use a formula such as =IF(F28=0,5,""). The true result is numeric, while the false result is an empty text string.
How can I sum text numbers without editing the source formulas?
In a helper column, enter =IF(A1="","",VALUE(A1)), replacing A1 with the source cell. Fill the formula down, then use SUM on the helper column.
Why does VALUE show an error in my helper column?
VALUE returns an error if it cannot interpret the source text as a number. Select the source cell and check for nonnumeric text or a number format Excel does not recognize.




