How to sum a range that contains errors in Excel

Q: Is there a built-in way to ignore errors?

In Excel, =AGGREGATE(9,6,C2:C8) sums while ignoring errors (9 = SUM, 6 = ignore errors). Google Sheets has no AGGREGATE; use the IFERROR version.

Q: How do I fix the errors at the source?

Give the lookup a fallback: =B2*XLOOKUP(A2,$E$2:$E$5,$F$2:$F$5,0) returns 0 for a missing SKU, or use IFNA to flag it.

Excel formula

=SUM(IFERROR(C2:C8,0))

Turn the errors into 0 before adding with =SUM(IFERROR(C2:C8,0)), wrapped in ARRAYFORMULA in Google Sheets.

Result on the example: $529.00 in H1
Works in: Every current version
Google Sheets: Different formula, see below

See it work

The formula is already in H1. Change any input and the result updates, or edit the formula to experiment.

How it works

One error in a range makes SUM return that error. IFERROR replaces each error in C2:C8 with 0 and leaves the numbers alone, so SUM can add them. Here the two unknown SKUs show #N/A and count as 0.

=SUM(IFERROR(C2:C8,0))

Result529

C2:C87 rows × 1 col: $50.00, $96.00, $43.50, #N/A, …
A range: 7 cells in one column.
IFERROR(C2:C8,0){7 rows × 1 col} 50, 96, 43.5, 0, …
IFERROR: Returns a fallback when the value is any error.
- valueC2:C87 rows × 1 col: $50.00, $96.00, $43.50, #N/A, …
- value_if_error0not used
SUM(IFERROR(C2:C8,0))529
SUM: Adds numbers and ranges. Skips text and blanks.
- number1IFERROR(C2:C8,0){7 rows × 1 col} 50, 96, 43.5, 0, …

In Google Sheets

Google Sheets needs a different formula: =ARRAYFORMULA(SUM(IFERROR(C2:C8,0))). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMIF(C2:C8,"<9.99E+307")	$529.00	Works in every version without array entry: the condition "less than a huge number" matches every number and no error.
=SUM(FILTER(C2:C8,ISNUMBER(C2:C8)))	$529.00	Keeps only the numbers, then adds them. Excel 365 and Google Sheets.

Common mistakes

Hiding errors also hides real problems. Here the #N/A means two SKUs are missing from the price list, and the order total is understated until they are added.
In Excel 2019 and older, =SUM(IFERROR(C2:C8,0)) needs Ctrl+Shift+Enter. The SUMIF version doesn't.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Sum a range that contains errors

In H1, total the line totals in C2:C8, skipping the lines that show #N/A.

Questions

Is there a built-in way to ignore errors?

In Excel, =AGGREGATE(9,6,C2:C8) sums while ignoring errors (9 = SUM, 6 = ignore errors). Google Sheets has no AGGREGATE; use the IFERROR version.

How do I fix the errors at the source?

Give the lookup a fallback: =B2*XLOOKUP(A2,$E$2:$E$5,$F$2:$F$5,0) returns 0 for a missing SKU, or use IFNA to flag it.

How do I count the errors I skipped?

=SUMPRODUCT(--ISERROR(C2:C8)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.