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 ^{1} and Reference^{2}. 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:

- 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.
- 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.
- AGGREGATE is
**not**available in Excel 2003 or 2007.

## Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

Marilyn Costache says

Hi- I have a question on the power query functions. When I edit my sheet in power query and then a few months later I add another sheet into it, will it automatically update the entire contents to include the edits that I originally made to it such as deleting columns, editing names, etc.?

Mynda Treacy says

Hi Marilyn,

Yes, if you set it up correctly in the first place. I couldn’t tell from your description where you were planning on editing columns etc.; the source data/table or within the Power Query editor itself.

I explain how to set your queries up so they pick up new and changes to data upon a refresh in my Power Query course. I see you are a member so you’ll find this topic covered in session 2.

Kind regards,

Mynda

Eddie says

I love the way aggregate works. My new best friend.

Philip Treacy says

Thanks Eddie.

Glad you like it.

Phil

Julian says

Even Microsoft had no such clear and in-depth explanations as you did. In addition, using function number 14 to substitue array formula Max If is really a brilliant inspiration.

Mynda Treacy says

Aw, thanks Julian 🙂 Glad you found it useful.

Mynda

Glenn Case says

Mynda:

Thanks for the tutorial. I had not heard of AGGREGATE previously. Amazing how every day there’s something else to learn.

In your spreadsheet, in cells C4:C5, you have a function which returns #NAME?. The contents are =_xlfn.FORMULATEXT(B4) and …(B5) respectively. I looked up Formulatext, and found it is a function which only works on versions after but not including Excel 2010, but I could find no info on xlfn.Formulatext. Can you educate me on that?

Mynda Treacy says

Hi Glenn,

The FORMULATEXT function is new in Excel 2013, which is why your version is returing the #NAME error. Sorry, I forgot to remove the formual from the download file. I actually wasn’t going to include the file originally, and then did so at the last minute. Oops.

Kind regards,

Mynda

Kevin Lehrbass says

AGGREGATE function is extremely useful and also underused. Thanks Mynda!

Mynda Treacy says

Cheers, Kevin. I’m guilty of underusing it 🙂

Mynda

Kevin Lehrbass says

Me too. I’ve had more “Ahhh….I could’ve used AGGREGATE function” moments than I’ve had “Cool, I just used AGGREGATE function!”

Mynda Treacy says

😀

Jon Acampora says

Great article Mynda! I agree with Kevin and this article is a good reminder to use the AGGREGATE function more often. I think (hope) we are slowly getting past the point where a lot of users are on 2007, and this wouldn’t be compatible for them. If everyone in your organization is on 2010 or later then it’s a great function to use. Thanks!

Mynda Treacy says

Thanks, Jon. Glad you liked it 🙂

Mynda