Sum a Range That Contains Errors

2010+AGGREGATE

One #N/A and a plain SUM returns an error too. AGGREGATE totals a range while skipping errors (and optionally hidden rows) — no cleanup needed.

Quick formula: sum B2:B100 ignoring any errors:

=AGGREGATE(9, 6, B2:B100)

Function 9 is SUM; option 6 means “ignore error values.” The total comes through even with errors present.

Functions used (tap for the full reference guide):

AGGREGATE SUM

The example

A column with a stray error still totals.

	A	B
1	Value
2	100
3	#N/A
4	50
5	AGGREGATE sum	150

The formula

SUM that skips errors:

=AGGREGATE(9, 6, B2:B100) // 9=SUM, 6=ignore errors

How it works

AGGREGATE is SUM/AVERAGE with skip options:

The first argument picks the function: 9 = SUM, 1 = AVERAGE, 4 = MAX, etc.
The second sets what to ignore: 6 = errors, 5 = hidden rows, 7 = both.
The third is the range. Errors no longer poison the total.
Older Excel (or a simpler need): =SUMIF(B2:B100, "<>#N/A") works for a specific error, or wrap each cell’s formula in IFERROR upstream.

Ignore hidden rows too: option 7 skips both errors and rows hidden by a filter — handy for totals that should reflect only what’s visible. AGGREGATE needs Excel 2010+.

Try it: interactive demo

Live demo

Values (use “err” for an error).

Sum (ignoring errors):

Variations

Ignore errors + hidden

Option 7:

=AGGREGATE(9, 7, B2:B100)

Average ignoring errors

Function 1:

=AGGREGATE(1, 6, B2:B100)

SUMIF alternative

Skip a known error:

=SUMIF(B2:B100, "<>#N/A")

Pitfalls & errors

2010 or later. AGGREGATE isn’t in Excel 2007.

Know the option codes. 6 = errors, 5 = hidden rows, 7 = both. The wrong code includes what you meant to skip.

Better to fix upstream. If you can, wrap source formulas in IFERROR so the data is clean — AGGREGATE is the patch when you can’t.

Practice workbook

📊

Download the free Sum a Range That Contains Errors practice workbook

An error-tolerant sum with the hidden-rows, average, and SUMIF variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum a range that has errors in Excel?

Use =AGGREGATE(9, 6, range). Function 9 is SUM and option 6 ignores error values, so the total comes through. Requires Excel 2010+.

How do I also ignore hidden rows?

Use option 7: =AGGREGATE(9, 7, range) skips both errors and filter-hidden rows.

What if I have an older Excel?

Wrap source formulas in IFERROR, or use =SUMIF(range, "<>#N/A") to skip a specific error.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: AGGREGATE · SUM