How to use AGGREGATE Function(WS)

Description

The Microsoft Excel AGGREGATE function allows you to apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows.

It was created by Microsoft to address the limitations of conditional formatting. Some conditional formatting can not be applied if there are errors in the range. The Excel AGGREGATE function allows you to ignore errors or hidden rows.

Syntax

There are 2 syntaxes for the AGGREGATE function:

REFERENCE Syntax

The REFERENCE syntax for the Microsoft Excel AGGREGATE function is:

AGGREGATE( function, options, reference1, [reference2], ... )

ARRAY Syntax

The ARRAY syntax for the Microsoft Excel AGGREGATE function is:

AGGREGATE( function, options, array, [optional_argument] )

Parameters or Arguments

function

The function that you wish to use and can be any of the following values:

Value	Explanation
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

Specifies which values to ignore when applying the function to the range. If theoptions parameter is omitted, it assumes that options is set to 0. options can be any of the following values:

Value	Explanation
0	Ignore nested SUBTOTAL and AGGREGATE functions
1	Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows
2	Ignore nested SUBTOTAL, AGGREGATE functions, and error values
3	Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows, and error values
4	Ignore nothing
5	Ignore hidden rows
6	Ignore error values
7	Ignore hidden rows and error values

reference1

The first numeric argument for the function when using the REFERENCE syntax.

reference2, ...

Optional. Numeric arguments 2 through 253 for the function when using the REFERENCE syntax.

array

An array, array formula, or reference to a range of cells when using the ARRAY syntax.

optional_argument

A second argument required if using the LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, or QUARTILE.EXC when using the ARRAY syntax.

Applies To

• Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010

Type of Function

• Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel AGGREGATE function examples and explore how to use the AGGREGATE function as a worksheet function in Microsoft Excel:

Let's look at a few examples based on the Excel spreadsheet above:

This first example (REFERENCE syntax) returns the AVERAGE for the range A2:A7 but ignores all error values. If you ran the AVERAGE(A2:A7) function directly, it would return #NUM! error because of the errors found in cells A4 and A7. The AGGREGATE function allows you to calculate an AVERAGE but ignore error values. The AGGREGATE function below would instead return 20.25 (instead of #NUM!).

=AGGREGATE(1, 6, A2:A7)

This next example (REFERENCE syntax) returns the MAX for the range A2:B7 but ignores all error values and hidden rows. If you ran the MAX(A2:B7) function directly, it would return #NUM! error because of the errors found in cells A4, A7, and B5. The AGGREGATE function allows you to calculate the MAX but ignore error values and hidden rows. The AGGREGATE function below would instead return 34 (instead of #NUM!).

=AGGREGATE(4, 7, A2:B7)

This final example (ARRAY syntax) uses LARGE to return the 2nd largest value for the range A2:B7 but ignores all error values. If you ran the LARGE(A2:B7,2) function directly, it would return #NUM! error because of the errors found in cells A4, A7, and B5. The AGGREGATE function allows you to calculate the 2nd LARGEST value but ignore error values. The AGGREGATE function below would instead return 26 (instead of #NUM!).

=AGGREGATE(14, 6, A2:B7, 2)