Statistical
AGGREGATE function: summarize ranges while ignoring errors and hidden rows
Calculate sums, averages, medians, and percentiles with AGGREGATE option codes that skip error cells and filtered-out rows.
Summarize ranges containing error cells without helper columns
Standard SUM(B2:B6) or AVERAGE(B2:B6) fails as soon as a single cell in the range evaluates to #DIV/0! or #N/A. AGGREGATE(function_num, options, ref1) lets you choose function 9 (SUM) or 1 (AVERAGE) with option 6 (ignore error values).
Option 6 skips error cells while still summing valid numeric entries in the range.
=AGGREGATE(9, 6, B2:B6)Large and Small positional queries ignoring errors
Functions 14 (LARGE) and 15 (SMALL) accept a fourth k argument while ignoring errors under option 6.
=AGGREGATE(14, 6, B2:B6, 1)Try the example
Use the original values or change them to test your understanding.