Quick Links
Excel’s AGGREGATE function lets you perform calculations whilst ignoring hidden rows, errors, or other functions that appear in the data. It’s similar to the SUBTOTAL function but provides more calculation options and gives you more control over what you want to exclude from the calculation.
The AGGREGATE Syntax
Before we look at some examples of the AGGREGATE function in use, let’s see how it works. The AGGREGATE function has two syntaxes—one for references and one for arrays—though you don’t need to get yourself tied up in knots over which one you’re using, as Excel selects the relevant one depending on the arguments you input. you may see both syntaxes in use when I show you some examples soon.
The Reference Form Syntax
The syntax for the reference form of the AGGREGATE function is:
where
The Array Form Syntax
On the other hand, if you’re working with arrays, the syntax is:
Functions and Exclusions (Argumentsaandb)
When entering argumentsaandbin either syntax form above, you’ll have various options to choose from.
The table below shows the different functions you can use in the AGGREGATE calculation (argumenta). Even though you might be tempted to type the function name, remember that this argument must be a number that represents the function you want to use. Functions 1 to 13 are for use with the reference form syntax, and functions 14 to 19 are for use with the array form syntax.
1
The arithmetic mean
2
The number of cells that contain numeric values
3
The number of cells that are not empty
4
The largest value
5
The smallest value
6
A multiplication
7
The simple standard deviation
8
The population-based standard deviation
9
An addition
10
The simple variation
11
The population-based variance
12
The middle value
13
The most frequently occurring number
14
Thenth largest value
15
Thenth smallest value
16
Thenth percentile, with the first and last values included
17
Thenth quartile, with the first and last values included
18
Thenth percentile, with the first and last values excluded
19
QUARTILE.EXC
Thenth quartile, with the first and last values excluded
This table lists the numbers you’re able to input to exclude certain values when creating your AGGREGATE formula (argumentb):
0
Nested SUBTOTAL and AGGREGATE functions
Hidden rows, and nested SUBTOTAL and AGGREGATE functions
Errors, and nested SUBTOTAL and AGGREGATE functions
Hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions
Hidden rows only
Errors only
Hidden rows and errors
Now, let’s look at some examples of how you can use the AGGREGATE function in real-world scenarios.
Example 1: Using AGGREGATE to Ignore Errors
This Excel spreadsheet contains a list of soccer players, the number of games they’ve played, the number of goals they’ve scored, and their game-per-goal ratios. Your aim is to work out the average game-per-goal ratio for all the players combined.
If you were to usethe AVERAGE functionalone by typing:
into cell C1, this would return an error, because the referenced range contains #DIV/0! errors.
Instead, using the AGGREGATE function gives you the option to ignore these errors and return the average for the remaining data. To do this, in cell C2, you need to type:
An alternative way to achieve the same outcome would be to usethe IFERROR functionin column D to replace any errors with a blank value.
Example 2: Using AGGREGATE to Ignore Hidden Rows (Reference)
Using the same spreadsheet, your next target is to calculate the total number of goals the team has scored.
One way to display totals is to check “Total Row” in the Table Design tab on the ribbon, which places the totals at the bottom of the table. However, if you’re working with a large dataset, constantly scrolling down to see the totals could waste time. Instead, consider placing the totals at the top of the spreadsheet outside the formatted table, so that they’re always on display.
Specifically, you want to display two totals. The first is the overall total when you combine the goals scored by all players, but the second is the total of only the players showing in the table after you apply filters.
To calculate the overall total, in cell C1, type:
Now, even after you apply a filter to one of the columns, such as displaying only the players who have played 15 games or more, the SUM formula you just applied still includes the rows that are filtered out.
This is where the AGGREATE function will save the day, as it allows your calculation to ignore rows that are filtered out. In fact, the AGGREGATE function would also work if you wanted to ignorerows you’ve hiddenby right-clicking the row header and clicking “Hide.”
Now, notice that the result of this formula differs from the result of the SUM formula you used in cell C1, because it considers only the rows on display.
Example 3: Using AGGREGATE to Ignore Hidden Rows (Array)
Next, let’s say you wanted to list the two highest goal tallies for players who have played 20 games or fewer.
You could apply the filter first and then generate your formula, but for the purposes of this demonstration, let’s create the formula first.
When you press Enter, notice that the result is a spilled array covering cells C1 and C2 because you told Excel to return the toptwovalues.
Now, filter the Games Played column to include only those players who have played 20 games or fewer, and see that the result of the AGGREGATE formula you entered earlier changes to ignore the hidden rows.
Things to Note When Using the AGGREGATE Function
Before you go ahead and use the AGGREGATE function in your own Excel workbooks, take a moment to note the following pointers:
Another way to hide rows in Excel tables so that the AGGREGATE function only includes what’s showing is byinserting slicers, interactive buttons that you can click to make filtering much more straightforward.
