ExamplesFor You
Home / Excel / AGGREGATE
✍️ 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

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 ExcelSUMIF Examples in ExcelSUMIFS Examples in Excel📊 All Excel examples

FAQ

Can this formula use Excel Tables?

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

💡 Useful resources

Here are some ideas for you

Optional resources that may help if you are learning formulas, building reports, or working in spreadsheets often.

  • 📘
    Excel formula books

    Practice formulas with structured examples you can keep beside your desk.

    See ideas
  • ⌨️
    Excel shortcut guides

    Build speed with keyboard shortcuts for selection, formatting and navigation.

    See ideas
  • 🔢
    Numeric keypads

    Helpful if you enter many numbers on a laptop or compact keyboard.

    See ideas
  • 🖥️
    External monitors

    Useful for viewing large worksheets, formulas and reference tables side by side.

    See ideas
  • 📒
    Desk notebooks

    Sketch formula logic, report ideas and table structures before building.

    See ideas
  • 💻
    Laptop stands

    Make long spreadsheet sessions more comfortable and ergonomic.

    See ideas

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