How to sum a column that contains errors
✓ Verified in LibreOffice 25.8.7.3Get a total even when some cells are #N/A or #DIV/0! — without cleaning the data first.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =AGGREGATE(9,6,A2:A6) | Function 9 = SUM, option 6 = ignore errors. Excel/LibreOffice; not in Google Sheets. |
| Google Sheets | =SUMIF(A2:A6,"<>#N/A") | Sheets has no AGGREGATE. This handles #N/A; for all error types use =SUM(IFERROR(A2:A6,0)) entered as an array. |
| LibreOffice Calc | =AGGREGATE(9,6,A2:A6) | Same as Excel. |
How it works
A plain SUM propagates any error — one #DIV/0! in the range makes the whole total an error. AGGREGATE(9,6,range) sums (function code 9) while ignoring errors (option 6), so the two good-plus-good rows total 10+20+30 = 60 despite the #DIV/0! and #N/A in between. AGGREGATE lives in Excel and LibreOffice but NOT Google Sheets — there, wrap the values in IFERROR (=SUM(IFERROR(A2:A6,0)) entered with Ctrl+Shift+Enter in older versions, or it just spills in modern ones), or SUMIF around specific error types. The same AGGREGATE trick does error-ignoring AVERAGE (code 1), MAX (4), COUNT (2) and more.
Verified, not just documented
We ran =AGGREGATE(9,6,A2:A6) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 60 — exactly the expected result. Every formula here is confirmed by actually executing it.