Article updated on March 27, 2018

The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature is to exclude hidden rows and error values in calculations.

It is designed for columns of data or vertical ranges.

The formula above sums all values in cell range B3:B7 excluding errors.

Formula in cell D3:

=AGGREGATE(9,6,B3:B7)

Excel Function Syntax

Reference form

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

Array form

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

Arguments

function_num Required. A number 1 to 19 that lets you choose between different functions. See list below.

 

Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN.
6 PRODUCT
7 STDEVS.S
8 STDEVS.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 allows you to choose which values to exclude in the calculation.

 

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 necessary for certain functions, see list below.

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

Comments

The function will not exclude hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, for example:

=AGGREGATE(14,3,A1:A10*(A1:A10<1),1)

When you type the first argument in the AGGREGATE function Excel returns a list of functions that are valid.