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