How to Sum While Ignoring Errors with AGGREGATE

AGGREGATE sums, averages and ranks a range while skipping #N/A and #DIV/0! — no IFERROR helper column needed.

You will learn to

• Sum a column that contains errors
• Average a calculated column with #DIV/0! values
• Find the k-th largest value while ignoring errors

How it works

A single #N/A or #DIV/0! makes SUM, AVERAGE and MAX return that error too. AGGREGATE(function_num, options, array, [k]) runs the same calculations but can skip errors: =AGGREGATE(9,6,E2:E8) sums E2:E8 ignoring error values (9 = SUM, 6 = ignore errors).

Other useful function numbers are 1 (AVERAGE), 4 (MAX), 14 (LARGE) and 15 (SMALL); the last two take a k argument, so =AGGREGATE(14,6,E2:E8,2) returns the second-largest value. Option 7 ignores both errors and hidden rows.

=AGGREGATE(9,6,E2:E8)