# How to sum a range that contains errors in Excel

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

**Excel:** `=SUM(IFERROR(C2:C8,0))` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(SUM(IFERROR(C2:C8,0)))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| SKU | Qty | Line total |  | SKU | Price | Order total |
| K-100 | 4 | =B2*XLOOKUP(A2,$E$2:$E$5,$F$2:$F$5) |  | K-100 | 12.5 |  |
| K-220 | 2 | =B3*XLOOKUP(A3,$E$2:$E$5,$F$2:$F$5) |  | K-220 | 48 |  |
| K-310 | 6 | =B4*XLOOKUP(A4,$E$2:$E$5,$F$2:$F$5) |  | K-310 | 7.25 |  |
| K-999 | 1 | =B5*XLOOKUP(A5,$E$2:$E$5,$F$2:$F$5) |  | K-450 | 89 |  |
| K-450 | 3 | =B6*XLOOKUP(A6,$E$2:$E$5,$F$2:$F$5) |  |  |  |  |
| K-500 | 5 | =B7*XLOOKUP(A7,$E$2:$E$5,$F$2:$F$5) |  |  |  |  |
| K-310 | 10 | =B8*XLOOKUP(A8,$E$2:$E$5,$F$2:$F$5) |  |  |  |  |

Formula in H1: `=SUM(IFERROR(C2:C8,0))`

Result: $529.00

## 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.

## Other ways

- `=SUMIF(C2:C8,"<9.99E+307")`: 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)))`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-ignoring-errors