Excel: Sum Cells Containing N/A and Ignore Errors
Question details
The user needs to sum selected cells while handling either text such as “N/A” or actual #N/A error values.
- Product
- Microsoft Excel or WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains values in cells E10, I10, M10, and Q10, and some cells may display N/A instead of numbers.
- Observed behavior
- SUM ignores the text string “N/A,” but an actual #N/A error can cause the total formula to return an error.
Select one affected cell and check the formula bar to determine whether it contains the text string “N/A” or the actual #N/A error value. Confirm the cells to include, such as E10, I10, M10, and Q10.
Use AGGREGATE to sum values while ignoring actual errors
Use this method when one or more referenced cells contain real Excel error values such as #N/A.
AGGREGATE with function number 9 performs a sum, while option 6 ignores error values in the referenced cells. Text such as “N/A” is also excluded from the calculation.
Click the cell where you want the result to appear.
Type =AGGREGATE(9,6,E10,I10,M10,Q10) in the formula bar or directly into the cell.
Press Enter. The selected numeric values are added while errors such as #N/A are ignored.
Use SUM for cells containing the text N/A
Use this simpler method when N/A is entered as text rather than stored as an error value.
Convert errors to zero with IFERROR before summing
Use this option when you want each error value to behave explicitly as zero within the calculation.
Sum Spreadsheet Cells Without Returning Errors
WPS Spreadsheets supports SUM, AGGREGATE, and IFERROR formulas, allowing you to total selected cells while ignoring text such as “N/A” or handling actual error values such as #N/A.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the affected cells.
- 2. Inspect the cell content: Click an N/A cell and check the formula bar to identify whether it contains text or an actual error value.
- 3. Enter the appropriate formula: Select the result cell and enter =SUM(E10,I10,M10,Q10) for text N/A, or =AGGREGATE(9,6,E10,I10,M10,Q10) to ignore errors.
- 4. Apply the calculation: Press Enter and verify that the total includes the numeric values without displaying an error.
Frequently Asked Questions
Why does SUM ignore “N/A” but fail on #N/A?
“N/A” in quotation marks is ordinary text, so SUM skips it. #N/A is an error value, which can cause a normal SUM calculation to return an error.
What formula sums cells while ignoring #N/A errors?
Use =AGGREGATE(9,6,E10,I10,M10,Q10). The 9 specifies SUM, and option 6 tells the spreadsheet to ignore error values.
How can I treat every error as zero in a total?
Wrap each reference in IFERROR, as in =SUM(IFERROR(E10,0),IFERROR(I10,0),IFERROR(M10,0),IFERROR(Q10,0)). This converts errors to zero before summing.
Do I need AGGREGATE if the cells only contain the text N/A?
No. If the cells contain the text string N/A, =SUM(E10,I10,M10,Q10) is sufficient because SUM ignores text references.
