Fix an Excel SUM Formula with Blank Cells or #N/A Errors
Question details
Correct a SUM formula that multiplies values when a referenced cell may return blank text or an error.

- 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.
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.
Correct the SUM and multiplication syntax
Use this formula when C6 and D6 should be added before the result is multiplied by B6.
Select the result cell and enter =SUM(C6:D6)*B6 in the formula bar.
Press Enter and confirm that the result equals the sum of C6 and D6 multiplied by B6.

Return zero when the lookup finds no match
Use IFNA in the lookup formula when a missing match should count as zero.
Replace calculation errors with zero
Use IFERROR when your business rule allows any error in the calculation to produce zero.
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. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Correct the calculation: Select the result cell, enter =SUM(C6:D6)*B6 in the formula bar, and press Enter.
- 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. Save the workbook: Use File > Save As to save the corrected workbook in an Excel-compatible format.

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.




