Excel AGGREGATE Function

The AGGREGATE Function is the Swiss Army Knife of functions; it’s so versatile it can replace these 19 Excel functions in one fell swoop:

But that’s not reason enough to use it, the function-killer reason to use AGGREGATE over any of the 19 standard functions it can replace is because it is far more powerful.

Excel AGGREGATE Function Options

You see, the second argument of the AGGREGATE function is ‘Options’, which enables you to tell it how to handle hidden rows, error values, and nested SUBTOTAL and AGGERGATE functions by specifying an option number from the list below:

AGGREGATE can handle Arrays

AGGREGATE has two forms, Array ¹ and Reference². You’ll notice when you type in the function that you have a choice (kind of):

The ‘form’ is dictated by the function_num you choose. Functions 1 to 13 are Reference form and 14 to 19 are array form.

And unlike regular array formulas, AGGREGATE doesn’t require you to enter the formula with CTRL+SHIFT+ENTER in array form.

AGGREGATE in Reference Form

Let’s look at how it compares to the SUM function, which means we’ll use the Reference form, as SUM is function_num 9.

The syntax:

=AGGREGATE(function_num, options, ref1, …)

My data is in an Excel Table called ‘Table1’, so I’m using the table Structured References in my formula. e.g. Table1[Viewers] refers to cells C8:C19.

I’ve used Option number 5, which ignores hidden rows. And you can see in the image below that when there are no rows hidden both formulas return the same result (cells B4 and B5):

However when I hide some rows, by filtering out the South and West regions, my AGGREGATE formula returns a result that ignores the hidden rows, whereas SUM still includes the hidden rows:

Note: you can hide rows by either selecting the row(s) > right-click > Hide, or through the use of Filters on your data.

AGGREGATE in Array Form

Similarly we can find the largest value in the Viewers column with function_num 14 for LARGE.

This means we’re using AGGREGATE in array form. And notice we also have an extra argument to specify ‘k’, with 1 returning the largest, 2 returning the second largest and so on:

With no rows hidden LARGE and AGGREGATE return the same result (see below):

But with rows for West hidden we get different results with AGGREGATE, as you can see below (note; I've used option number 5 - ignore hidden rows in AGGREGATE):

Exploiting Option 6 – Ignore Error Values

So far we’ve only looked at Option number 5, which ignores hidden rows. Option number 6 allows us to ignore error values, and we can use this to our advantage to only aggregate values that meet a certain condition, or criteria.

For example, let’s say we want to find the MAX, IF the region is North. Normally we’d use a nested MAX and IF array formula, but with AGGREGATE we can use the LARGE function_num 14, like so:

Let’s take a closer look at this formula (from cell B5 above) and how it evaluates:

=AGGREGATE(14,6,Table1[Viewers]/(Table1[Region]="North"),1)

The array argument takes the values in the Viewers column and then uses Boolean logic (Table1[Region]="North") to test if the region = North

When (Table1[Region]="North") is evaluated it returns a series of TRUE and FALSE values like this:

=AGGREGATE(14,6,Table1[Viewers]/{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE},1)

In Excel when a math operation is performed on a logical value (TRUE or FALSE), they are converted to their numeric equivalent of 1 and 0.

So when the Table1[Viewers] array is expanded Excel takes the values in the Viewers column divided by the array of 1’s and 0’s:

=AGGREGATE(14,6, {91;87;99;102;125;140;107;133;138;172;206;240}/ {1;0;0;0;0;0;0;1;1;0;0;0},1)

And when we divide anything by zero we get an error, so our formula is littered with #DIV errors for values in the Viewers column where the Region isn’t North, like this:

=AGGREGATE(14,6,{91;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;133;138;#DIV/0!;#DIV/0!;#DIV/0!},1)

Have you worked out where I’m going with this? Remember the Option number argument of 6 ignores errors, so AGGREGATE now looks like this with all the #DIV errors gone:

=AGGREGATE(14,6,{91;133;138},1)

And the 1^st Largest value is:

=138

Likewise we can use SMALL as an alternative to a MIN IF array formula.

If the concept of arrays is hurting your head and you want to learn more about array formulas (I don’t know, you might be a glutton for punishment ;-)), then check out this excellent book.

AGGREGATE Notes:

1. As with many Excel functions, AGGREGATE is designed to work with data in columns, so when you reference a horizontal range, AGGREGATE will not ignore values in hidden columns. It only ignores hidden rows in a vertical range.
2. You might have noticed AGGREGATE is similar to the SUBTOTAL function in that it can perform a range of different calculations, and you’d be right, but I’m sure you’ll agree that AGGREGATE is much more powerful.
3. AGGREGATE is not available in Excel 2003 or 2007.
4. AGGREGATE will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, as it does in the last example (=AGGREGATE(14,6,Table1[Viewers]/(Table1[Region]="North"),1)). i.e. option numbers 0 through 5 and 7 will not ignore hidden rows.