A lookup column has a few #N/A for products not found, and now the total at the bottom shows #N/A too. You can fix the lookups, or for a quick report, total around them.

AGGREGATE: the built-in answer

=AGGREGATE(9, 6, D2:D200)      ' SUM, ignoring errors
=AGGREGATE(1, 6, D2:D200)      ' AVERAGE
=AGGREGATE(4, 6, D2:D200)      ' MAX
=AGGREGATE(14, 6, D2:D200, 3)  ' 3rd largest

The first argument is the function (1 AVERAGE, 2 COUNT, 4 MAX, 5 MIN, 9 SUM, 14 LARGE, 15 SMALL). The second is what to ignore: 6 errors, 5 hidden rows, 7 both, 3 hidden rows + errors + nested subtotals.

Other ways

=SUMIF(D2:D200, "<9.9E+307")       ' sums only real numbers, skips errors and text
=SUM(IFERROR(D2:D200, 0))          ' Excel 365 (Ctrl+Shift+Enter in older Excel)
=AVERAGE(IFERROR(D2:D200, ""))     ' blanks don't count in the average

With a condition

=SUM(IFERROR((B2:B200="North") * D2:D200, 0))
=AGGREGATE(14, 6, D2:D200 / (B2:B200="North"), 1)    ' max for North

The divide-by-condition trick is worth remembering: rows that fail the test become #DIV/0! errors, which AGGREGATE skips.

Count the errors so they don’t stay hidden

=SUMPRODUCT(--ISERROR(D2:D200))
=SUMPRODUCT(--ISNA(D2:D200))        ' only #N/A

Show that count next to the total (“3 lines not found”). A silently smaller total is worse than an error.

Better: fix at source

If the #N/A comes from XLOOKUP, give it a visible fallback such as =XLOOKUP(A2, P[Code], P[Price], "Missing"), then find and correct the missing codes.

Where people go wrong

Practice

Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.

More: all formula combos · Excel function course.