Sum, Average or Max While Ignoring #N/A and Errors in Excel (AGGREGATE)

Sum, Average or Max While Ignoring #N/A and Errors in Excel (AGGREGATE) featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 2 min read

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.

In this article
  1. AGGREGATE: the built-in answer
  2. Other ways
  3. With a condition
  4. Count the errors so they don’t stay hidden
  5. Better: fix at source
  6. Where people go wrong
  7. Practice
  8. Continue learning

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.

Advertisement
πŸ’‘ Option 7 gives a filter-aware total that also skips errors, which is handy at the bottom of filtered reports.

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.

Advertisement

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

Mistake Why it hurts
Wrapping every formula in IFERROR(…,0) Hides typos and broken references too
IFERROR(…,0) before AVERAGE Zeros drag the average down; use “” instead
AGGREGATE option 6 with SUBTOTAL rows inside the range Double counts; use option 3 or 7

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.

Advertisement

πŸ“Ž Practice files for this article

  • πŸ“—
    Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days β€” with automatic checks.
    ⬇ XLSX Β· 39 KB

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong