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.
Enter your email address below to download the sample workbook.
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 Reference2. 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 1st 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.
- 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.
Sandeep Kothari
Great article Mynda.
Mynda Treacy
Thanks, Sandeep. Glad you liked it.
Kate
Will the Aggregate function work with text values, or only number values? Is there an equivalent for finding unique text values?
Mynda Treacy
Hi Kate,
AGGREGATE only works with numbers. I’m not sure what you’re hoping to do with the text values? Count them, highlight them, are they single words in cells, or are they phrases? Please post your question and sample Excel file on our Forum so we can see what you’re working with.
Mynda
Marilyn Costache
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
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
I love the way aggregate works. My new best friend.
Philip Treacy
Thanks Eddie.
Glad you like it.
Phil
Julian
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
Aw, thanks Julian ๐ Glad you found it useful.
Mynda
Glenn Case
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
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
AGGREGATE function is extremely useful and also underused. Thanks Mynda!
Mynda Treacy
Cheers, Kevin. I’m guilty of underusing it ๐
Mynda
Kevin Lehrbass
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
๐
Jon Acampora
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
Thanks, Jon. Glad you liked it ๐
Mynda