✍️ Excel summary examples

AGGREGATE Examples in Excel

Learn AGGREGATE examples for ignoring errors, hidden rows and filtered data.

Updated 2026-06-125 practical examplesCopy-ready

💡 Ideas for You

Learning resources for Excel formulas, spreadsheets and reporting.

4 useful links

📚Step-by-step books
Start with beginner-friendly step-by-step books and learning resources from Create & Learn.
📘Excel guide
Helpful references for formulas, examples, tables and everyday Excel tasks.
🧮Formula books
Books and references for spreadsheet formulas, lookups, summaries and examples.
📊Spreadsheet reporting
Resources for Excel reports, tables, dashboards and spreadsheet analysis.

Some links in this section may be affiliate links. Choose only what is useful for your own work.

What it does

AGGREGATE can summarize data while ignoring errors, hidden rows or nested subtotals.

Syntax or pattern

=AGGREGATE(function_num, options, array, [k])

✅

5 practical examples

1

Largest value ignoring errors

Return the largest valid number.

=AGGREGATE(14,6,B2:B100,1)

Function 14 is LARGE, option 6 ignores errors.

2

Smallest value ignoring errors

Return the smallest valid number.

=AGGREGATE(15,6,B2:B100,1)

Useful with messy calculated data.

3

Average ignoring errors

Average a range that may contain errors.

=AGGREGATE(1,6,B2:B100)

Function 1 is AVERAGE.

4

Sum ignoring hidden rows

Sum while ignoring hidden rows and errors.

=AGGREGATE(9,7,B2:B100)

Options change what is ignored.

5

Second largest value

Return the second largest value ignoring errors.

=AGGREGATE(14,6,B2:B100,2)

The final argument is k for LARGE/SMALL functions.

Common mistakes to avoid

Selecting ranges with different sizes.
Using text values where numbers are required.
Forgetting how blanks and hidden rows are treated.

Related Excel examples

SUM Examples in Excel SUMIF Examples in Excel SUMIFS Examples in Excel 📊 All Excel examples

FAQ

Can this formula use Excel Tables?

Yes. Structured references often make summary formulas easier to read.