Formula Errors

Fix Excel SUM Formulas That Reference Numbers Stored as Text

Aamir Naveed AkramAamir Naveed Akram Nov 20, 2026 868 views

Question details

The user needs an Excel SUM formula to include numeric values returned by other formulas as text.

Fix Excel SUM Formulas That Reference Numbers Stored 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.
Before you start

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.

Solution 1Recommended

Return a Number From the Source Formula

Use this method when you can edit the formulas that produce the values being summed.

1
Inspect the source formula

Select a cell referenced by SUM and check the formula bar for a quoted number, such as =IF(F28=0,"5","").

2
Remove the number's quotation marks

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.

3
Update and check the total

Apply the change to any other affected source formulas, then select the SUM cell and verify that its total includes the numeric results.

Return a Number From the Source Formula
Text and numbers are different: The value "5" is text, while 5 is numeric. SUM ignores text values in referenced cells.
Calculate with WPS Spreadsheets

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. 1. Open the workbook: In WPS Spreadsheets, select Open and choose the Excel workbook.
  2. 2. Find the source formula: Select a cell referenced by the SUM formula and inspect its formula in the formula bar.
  3. 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. 4. Check and save: Check the SUM cell, then use File > Save to save the workbook.
Return numeric values from IF formulas for accurate SUM totalsOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet formulas and a free, easy-to-use interface
microsoft office alternative - wps office

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.