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

summarize sales data. Use AGGREGATE to Performs aggregate calculations with ignore options. For range arguments, drag from the start cell to the end cell in the same argument field, or click start and end cells in order. use 9 for function_num, use 0 for options. Fill the argument fields below and complete a formula that achieves this goal.

Sample correct formula

=AGGREGATE(9, 0, D2:D8)

Practice This Function

Practice in Sequential ModeBack to Function List