Math and trigonometry
AGGREGATE Function
Applies one of 19 aggregation functions to a list or table, optionally ignores hidden rows or errors, and returns the
aggregate value.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

At first glance, the AGGREGATE function looks much like the
SUBTOTAL function. That is, you can use it to perform
calculations on your data while ignoring hidden rows. However, this function offers much more
power, as the examples illustrate.
Managers... 
Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more. 
Syntax
The AGGREGATE function has two Syntax
Forms,
as determined by its function_num. The following table
shows which form to use for which function_num.
Reference Form:
AGGREGATE(function_num, options, ref1, ref2,...)

function_num
Required. A number 1 through 13 to specify the
function, as shown in the Function_Num Table
below.

options
Required. A number that determines which values to ignore in
the evaluation range for the function, as shown in the
Options Table below.

ref1
Required. The first reference or numeric argument for which
you want the aggregate value.

ref2
Optional. References or numeric arguments 2 to 253 for which
you want the aggregate value.
Note: This form can NOT use arrays in
ref1, ref2, etc.
To illustrate, this formula...
=AGGREGATE(9, 6, Price*Units)
...tries to find the sum of total sales. But
the formula always returns a #VALUE! error, even if it's arrayentered.
Array Form:
AGGREGATE(function_num, options, array,
k)

function_num
Required. A number 14 through 19 to specify the
function, as shown in the Function_Num Table
below.

options
Required. A number that determines which values to ignore in
the evaluation range for the function, as shown in the
Options Table below.

array
Required. An array, an array formula, or a reference to a
range of cells for which you want the aggregate value.

k Required.
Each of the six ArrayForm functions requires a second
argument, which is specified here.
Note: The Array Form CAN use arrays in
array, of course.
To illustrate, this formula...
=AGGREGATE(15, 6, Price*Units, 2)
...successfully finds the secondsmallest value for total sales.
Warning: When using the Array Form, it's
easy to forget to include the k value. If you
do forget, the formula always returns a #VALUE! error.
Options
Table 
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 
Applies To
Excel 2010 and above
Remarks
Like SUMPRODUCT, the AGGREGATE function can treat arguments
as arrays without being arrayentered.
Examples
You can
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
The following examples reference the two versions of the data
shown here. For easy reference, the same two views of the data
appear after the last example.
In Data 1, AGGREGATE and AVERAGE return the same results when AGGREGATE's
function_num specifies AVERAGE, because no rows are hidden.
In Data 2, with several rows hidden, and with the Options argument specified to ignore
hidden rows, AGGREGATE does just that. But in the second example, AVERAGE includes the
hidden rows in its calculation. And in the third example, with the Options argument set to 6
(ignore errors), AGGREGATE returns the average of both both hidden and visible rows.
AGGREGATE returns a #VALUE! error when you use a Reference function, even if you
arrrayenter the formula.
AGGREGATE returns the secondlargest value in Sales1 for the Ties product.
This formula asks for the smallest value. But because (Prod1="Ties")*Sales1 returns the
array {349;0;0;0;0;907;0;1093;0;289}, the smallest value always will be zero…which isn't the
answer we need. The next example shows how to fix this problem.
The 6 in AGGREGATE's Options argument tells the function to ignore errors. Therefore, we
can ignore the zeros in the previous example by forcing them to be errors. To do so, we
divide the array into 1, giving us an array of ones and errors. So when we multiply by Sales,
we get the array: {349;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;907;#DIV/0!;1093;#DIV/0!;289}.
AGGREGATE now does what we want, returning the smallest of the nonerror results.
Note: Excel doesn't provide a MINIFS function, but this example shows how to fake it.
Much like the SUMPRODUCT function, we can add any number of filters by multiplying by a
series of tests. Notice the parentheses, however. Each test is enclosed in parentheses; the
group of all tests is enclosed prior to dividing into 1; and then the quotient is enclosed
before it's multiplied by Sales1.
This is a slightly shorter alternative to the previous example. Here, Sales1 is the first array.
So we can simply divide it by the array of 1s and errors. Use either version you want.
This example works like a MEDIANIFS function, which Excel doesn't offer. It uses the
QUARTILE.INC function to return Quartile 2, which is also the
MEDIAN value. Here, South has
seven Sales values and 749 is the middle one.
Here's a repeat of the two versions of the data from above.
Other ExcelUser
Information About SUMPRODUCT
Other Help
