The AGGREGATE function returns 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.

• This function was introduced in Excel 2010 and so is not available in earlier versions.
• There are 2 syntax for the AGGREGATE function – Reference and Array.

### Reference Syntax

=AGGREGATE(function_num,options,ref1,[ref2], … )

### Array Syntax

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

#### Arguments

Argument Description
function_num 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 A numerical value that determines which values to ignore in the evaluation range for the function

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,
[ref2], …
1 to 254 numeric argument for functions that take multiple numeric arguments for which you want the aggregate value. [ref1] is required, subsequent values are optional

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 Meaning of [k]
LARGE(array,k) Return the k’th largest value
SMALL(array,k) Return the k’th smallest value
PERCENTILE.INC(array,k) Return the k’th percentile
QUARTILE.INC(array,quart) Return the k’th quartile
PERCENTILE.EXC(array,k) Return the k’th percentile
QUARTILE.EXC(array,quart) Return the k’th quartile

#### Examples

A B C D E F
1 Data Data   Formula Result Notes
2 63 32   =AGGREGATE(4,6,A2:A11) 99 Calculates the maximum value while ignoring error values in the range
3 #DIV/0! 48   =AGGREGATE(15,6,A2:A11,3) 28 Calculates the 3rd largest value while ignoring error values in the range
4 72 54   =AGGREGATE(15,6,A2:A11) #VALUE! Will return #VALUE! error. This is because AGGREGATE is expecting a second ref argument, since the function SMALL requires one
5 30 95   =AGGREGATE(12,6,A1:A11,B1:B11) 64.5 Calculates the median while ignoring error values in the range
6 #NUM! 68   =MAX(A2:A5) #DIV/0! Will return error value, since there are error values in the evaluation range
7 21 58
8 99 72
9 22 66
10 81 93
11 28 100

#### Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

 • the function_num argument is any number less than 1 or greater than 19 • the options argument is any number less than 0 or greater than 7 • the function_num argument is between 14 and 19, denoting the LARGE, SMALL, PERCENTILE, or QUARTILE functions, and no argument [k] is supplied
#NUM! Occurs if any of the supplied arguments are non-numeric