For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

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, 2005-2014
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 array-entered.

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 Array-Form 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 second-smallest 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.

Function_Num Table
FUNCTION_NUM FUNCTION SYNTAX FORM
1 AVERAGE Reference
2 COUNT Reference
3 COUNTA Reference
4 MAX Reference
5 MIN Reference
6 PRODUCT Reference
7 STDEV.S Reference
8 STDEV.P Reference
9 SUM Reference
10 VAR.S Reference
11 VAR.P Reference
12 MEDIAN Reference
13 MODE.SNGL Reference
14 LARGE Array
15 SMALL Array
16 PERCENTILE.INC Array
17 QUARTILE.INC Array
18 PERCENTILE.EXC Array
19 QUARTILE.EXC Array

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 array-entered.

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 arrray-enter the formula.

AGGREGATE returns the second-largest 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 non-error 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

 

Charley's SwipeFile charts


Free Excel Dashboards