Excel Function

AGGREGATE

Performs aggregate calculations with ignore options.

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

Arguments

function_num

Required

AGGREGATE operation code: 1=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.

options

Required

Ignore options: 0/omitted=ignore nested SUBTOTAL/AGGREGATE, 1=ignore hidden rows + nested, 2=ignore errors + nested, 3=ignore hidden rows + errors + nested, 4=ignore nothing, 5=ignore hidden rows, 6=ignore errors, 7=ignore hidden rows + errors.

array

Required

Specify a reference range. You can drag or click start/end cells.

k

Optional

Enter a numeric value.

Practice Preview

Use AGGREGATE to aggregate Sales in column D with 9 (SUM). You can set ranges by dragging from the start cell to the end cell in the same argument field.

Example Table

#
AProduct ID
BProduct Name
CCategory
DSales
2P001AppleFood1200
3P002BananaFood980
4P003NotebookStationery450
5P004PenStationery300
6P005GrapesFood1500
7P006RulerStationery250
8P007PeachFood860

How to Use (Formula)

=AGGREGATE(9, 0, D2:D8)

Result

5540

Practice This Function

Start Function PracticeBack to Function List

AGGREGATE FAQ

How do I use AGGREGATE in Excel?

Use AGGREGATE when you want to summarize data while ignoring errors or hidden rows. Enter Function number, Options, Target range in that order, then add optional arguments when needed.

Where can I practice AGGREGATE?

Use the "Start Function Practice" button to jump straight into AGGREGATE drills.

What arguments does AGGREGATE use?

The main arguments are Function number, Options, Target range, Rank. Start with the required ones, then add optional arguments if needed.