AGGREGATE Function

Runs one of 19 calculations while skipping errors, hidden rows or nested subtotals.

Category

Math & Trig

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice AGGREGATE in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

AGGREGATE is SUBTOTAL's more powerful sibling. It offers 19 calculations — including MEDIAN, LARGE, SMALL and PERCENTILE — and an options number that decides what to skip: errors, hidden rows, other subtotals, or a mix.

Its best-known trick: summing or finding the largest value in a column that contains #N/A or #DIV/0! errors, without cleaning the data first.

Syntax

=AGGREGATE(function_num, options, ref1, [ref2], …) or =AGGREGATE(function_num, options, array, k)

Arguments

ArgumentRequired?What it does
function_numYes1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV.S, 8 STDEV.P, 9 SUM, 10 VAR.S, 11 VAR.P, 12 MEDIAN, 13 MODE.SNGL, 14 LARGE, 15 SMALL, 16 PERCENTILE.INC, 17 QUARTILE.INC, 18 PERCENTILE.EXC, 19 QUARTILE.EXC.
optionsYesWhat to skip: 0 nested subtotals, 1 + hidden rows, 2 + errors, 3 all three, 4 nothing, 5 hidden rows, 6 errors, 7 hidden rows and errors.
ref1 / arrayYesThe range (functions 1–13) or array (14–19) to calculate.
kFor 14–19Which largest/smallest value, percentile or quartile.

Examples

Example 1 — Totalling past the errors

A lookup column has #N/A for missing products. SUM fails; AGGREGATE with option 6 skips the errors.

C2fx=AGGREGATE(9, 6, B2:B5)
ABC
1ProductPriceTotal
2Widget4.520.5
3Gizmo#N/A
4Gadget12
5Cable4
=AGGREGATE(9, 6, B2:B5) Explain

Example 2 — Largest sale in one region

Dividing by a TRUE/FALSE test turns non-West rows into #DIV/0! errors, which option 6 then ignores.

D2fx=AGGREGATE(14, 6, B2:B5/(A2:A5="West"), 1)
ABCD
1RegionSalesTop West sale
2West620620
3East900
4West480
5West550
=AGGREGATE(14, 6, B2:B5/(A2:A5="West"), 1) Explain
Change the last argument to 2 for the second-largest, or use function 15 for the smallest.

Common mistakes

  • Array form needs 14–19. Only LARGE, SMALL, PERCENTILE and QUARTILE (14–19) accept a calculated array like B2:B5/(A2:A5="West"). With 1–13 it must be a real range.
  • Hidden columns aren't skipped. Like SUBTOTAL, the hidden options only apply to rows.

Notes

  • In the Practice Sandbox, AGGREGATE skips errors and nested subtotals; rows you hide by hand are still counted.
  • Related functions: SUBTOTAL, LARGE, SMALL, IFERROR.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.