How to sum a column that contains errors

✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)

Get a total even when some cells are #N/A or #DIV/0! — without cleaning the data first.

The formula

App	Formula	Notes
Excel (desktop)	`=AGGREGATE(9,6,A2:A6)`	Function 9 = SUM, option 6 = ignore errors. Excel/LibreOffice; not in Google Sheets.
Google Sheets	`=SUM(FILTER(A2:A6,ISNUMBER(A2:A6)))`	Google Sheets has no AGGREGATE — executed 2026-08-30, =AGGREGATE(9,6,A2:A6) returned #NAME? there against 60 in LibreOffice. FILTER keeps only the numeric cells, so error cells drop out before SUM sees them; =SUMIF(A2:A6,"<>#N/A") handles #N/A alone. Documented Sheets syntax, carrying no executed result of its own.
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, filter the errors out with =SUM(FILTER(A2:A6,ISNUMBER(A2:A6))), or wrap the values in IFERROR — =ARRAYFORMULA(SUM(IFERROR(A2:A6,0))), since Sheets needs ARRAYFORMULA to iterate over the range (in older Excel the same IFERROR wrap is entered with Ctrl+Shift+Enter). The same AGGREGATE trick does error-ignoring AVERAGE (code 1), MAX (4), COUNT (2) and more.

The Google Sheets alternative

Google Sheets needs different syntax for this task. The formula below is Sheets-specific — it was executed in Google Sheets (2026-08-30) against the same sample data as the worked example, and the value beside it is what Google returned. The LibreOffice column reads n/a because the formula is outside LibreOffice’s dialect, so there is nothing of ours to report there.

Formula	What it does	Returned by LibreOffice 25.8.7.3	Returned by Google Sheets (executed 2026-08-30)
`=SUM(FILTER(A2:A6,ISNUMBER(A2:A6)))`	FILTER + ISNUMBER drops the error cells before SUM sees them — Google Sheets has no AGGREGATE	n/a (Sheets-only formula)	60 Google Sheets alternative (executed 2026-08-30) NOTE: written with the PLAIN function name; the LibreOffice reference run executed the _xlfn. storage form of this formula, so the two runs are not byte-identical inputs

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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. For the worked example Google Sheets returned #NAME?, which is not what LibreOffice returned (60) — both values are shown as each engine produced them, and the disagreement itself is the finding. A further 1 row is a Google Sheets alternative: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for it. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

AGGREGATE · SUM · FILTER · ISNUMBER — see full Excel, Google Sheets & LibreOffice compatibility for each.