Formula Errors

Fix an Excel SUM Formula with Blank Cells or #N/A Errors

Emma BrownEmma Brown Nov 18, 2026 869 views

Question details

Correct a SUM formula that multiplies values when a referenced cell may return blank text or an error.

Fix an Excel SUM Formula with Blank Cells or #N/A Errors
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to add C6 and D6, then multiply the result by B6, while handling a lookup that may return an empty string or #N/A.
Observed behavior
The formula =SUM(C6;D6:*B6) has invalid syntax, and errors in referenced cells can propagate through the calculation.
Before you start

Check that the intended calculation is the sum of C6 and D6 multiplied by B6. Select D6 and inspect its formula bar to determine whether it returns an empty string or an error such as #N/A.

Solution 1Recommended

Correct the SUM and multiplication syntax

Use this formula when C6 and D6 should be added before the result is multiplied by B6.

1
Edit the formula

Select the result cell and enter =SUM(C6:D6)*B6 in the formula bar.

2
Check the result

Press Enter and confirm that the result equals the sum of C6 and D6 multiplied by B6.

Correct the SUM and multiplication syntax
Blank text and errors: SUM ignores empty text returned by a formula in a referenced cell, but it does not ignore an error such as #N/A. An error or nonnumeric value in B6 can also affect the multiplication.
Calculate with WPS Spreadsheets

Fix the SUM Formula in WPS Spreadsheets

Use WPS Spreadsheets to correct the SUM and multiplication formula, then handle lookup errors according to your calculation rules.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Correct the calculation: Select the result cell, enter =SUM(C6:D6)*B6 in the formula bar, and press Enter.
  3. 3. Handle a missing match: If D6 contains a lookup that returns #N/A, edit its formula to wrap the lookup in IFNA and return 0 when that matches your business rule.
  4. 4. Save the workbook: Use File > Save As to save the corrected workbook in an Excel-compatible format.
Add a cell range and multiply the result in one formulaHandle missing lookup matches with IFNAOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why is =SUM(C6;D6:*B6) invalid?

The colon must connect valid range references, and the multiplication is misplaced. To add C6 through D6 and multiply by B6, enter =SUM(C6:D6)*B6.

Does SUM ignore a formula that returns an empty string?

SUM generally ignores an empty string returned by a formula in a referenced cell. It does not ignore an error value such as #N/A in that cell.

How can I stop a missing lookup match from returning #N/A?

Wrap the lookup in IFNA, for example =IFNA(your_lookup,0), if a missing match should count as zero. Use a semicolon instead of the comma if your settings require it.

Should I use IFERROR or IFNA?

Use IFNA to handle #N/A specifically. Use IFERROR when replacing any error with the chosen fallback is acceptable, since it can also hide other formula problems.