AGGREGATE Function

The AGGREGATE function is one of the math and trigonometry functions. The function is used to return an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

Syntax

AGGREGATE(function_num, options, ref1, [ref2], ...)

The AGGREGATE function has the following arguments:

Argument	Description
function_num	A numeric value that specifies which function to use. The possible values are listed in the table below.
options	A numeric value that specifies which values should be ignored. The possible values are listed in the table below.
ref1	The first numeric value for which you want the aggregate value.
ref2	Up to 253 numeric values or a reference to the cell range containing the values for which you want the aggregate value. It is an optional argument.

The function_num argument can be one of the following:

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

The options argument can be one of the following:

Numeric value	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

Notes

If you want to use one of the following functions: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, or QUARTILE.EXC, ref1 must be a reference to the cell range and ref2 must be the second argument that is required for these functions (k or quart).

Function	Syntax
LARGE	LARGE(array, k)
SMALL	SMALL(array, k)
PERCENTILE.INC	PERCENTILE.INC(array, k)
QUARTILE.INC	QUARTILE.INC(array, quart)
PERCENTILE.EXC	PERCENTILE.EXC(array, k)
QUARTILE.EXC	QUARTILE.EXC(array, quart)

How to apply the AGGREGATE function.

Examples

The figure below displays the result returned by the AGGREGATE function when the SUM function is applied.

The figure below displays the result returned by the AGGREGATE function when the LARGE function is applied, ref1 is a reference to the cell range, and k is equal to 2. The function returns the second largest value in cells A1-A4.

Return to previous page