AGGREGATE function
Summary
Returns an aggregate in a list or database
Syntax
Reference form
AGGREGATE(function_num, options, ref1, [ref2], …)
Array form
AGGREGATE(function_num, options, array, [k])
The AGGREGATE function syntax has the following arguments:
Function_num Required. A number 1 to 19 that specifies which function to use.
Function_num
Function
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. A numerical value that determines which values to ignore in the evaluation range for the function.
Note: The function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, for example: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)
Option
Behavior
0 or omitted
Ignore nested SUBTOTAL and AGGREGATE functions
1
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2
Ignore error values, nested SUBTOTAL and AGGREGATE functions
3
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4
Ignore nothing
5
Ignore hidden rows
6
Ignore error values
7
Ignore hidden rows and error values
Ref1 Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.
Ref2,... Optional. Numeric arguments 2 to 253 for which you want the aggregate value.
For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:
Function
LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)
Array form
AGGREGATE(function_num, options, array, [k])
The AGGREGATE function syntax has the following arguments:
Function_num Required. A number 1 to 19 that specifies which function to use.
Function_num
Function
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. A numerical value that determines which values to ignore in the evaluation range for the function.
Note: The function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, for example: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)
Option
Behavior
0 or omitted
Ignore nested SUBTOTAL and AGGREGATE functions
1
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2
Ignore error values, nested SUBTOTAL and AGGREGATE functions
3
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4
Ignore nothing
5
Ignore hidden rows
6
Ignore error values
7
Ignore hidden rows and error values
Ref1 Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.
Ref2,... Optional. Numeric arguments 2 to 253 for which you want the aggregate value.
For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:
Function
LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)
Example
=AGGREGATE(4, 6, A1:A11)
=AGGREGATE(14, 6, A1:A11, 3)
=AGGREGATE(15, 6, A1:A11)
=AGGREGATE(12, 6, A1:A11, B1:B11)
=MAX(A1:A2)
=AGGREGATE(14, 6, A1:A11, 3)
=AGGREGATE(15, 6, A1:A11)
=AGGREGATE(12, 6, A1:A11, B1:B11)
=MAX(A1:A2)