How to Use AGGREGATE Function in Excel?

AGGREGATE Function in Excel

AGGREGATE Function in excel returns the aggregate of a given data table or data lists, this function also has the first argument as function number and further arguments are for a range of the data sets, the function number should be remembered to know which function to use.

Syntax

There are two syntaxes for the AGGREGATE Formula:

Reference Syntax

=AGGREGATE(function_num, options, ref1, ref2, ref[3],…)

Array Syntax

=AGGREGATE(function_num,options,array,[k])

Function_num is a number that denotes a specific function that we want to use, it is a number from 1-19

Option: it is also a numeric value ranging from 0 to 7 and determines which values are to be ignored while calculations

Ref1, ref2, ref[3]: is the argument while using the reference syntax, it is numeric value or values on which we want to perform the computation, at least two arguments are required rest arguments are optional.

Array: is an array of values on which we want to perform the operation, it is used in array syntax of the AGGREGATE function in excel

K: is an optional argument and is a numeric value, it is used when the function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC is used.

Examples

You can download this AGGREGATE Function Excel Template here – AGGREGATE Function Excel Template

Example – #1

Suppose we have a list of numbers and we will compute the Average, Count that is the number of cells that contains a value, Counta -count of cells that are not empty, Maximum, Minimum, product and sum of the given numeric values. Values are given below in the table:

Let us first calculate the Average in Row 9, for all given values. For average the function_ num is 1

In Column C, all values are given and we won’t have to ignore any values, so we will select the Option 4 (ignore nothing)

And selecting the range of values C1:C8 as an array of numeric values

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Since ‘k’ is an optional argument and is used when a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC is used but in this case, we are calculating the Average so we will omit the value of k.

So, the average value is

Similarly, for range D1:D8, again we will select option 4.

For range E1:E8, a cell E6 contains an error value, if we will use the same AGGREGATE formula we will get an error, but when an appropriate option is used, the AGGREGATE in Excel gives the average of the remaining values neglecting the error value in E6.

In order to ignore the error values, we have option 6.

Similarly, for range G1:G8 we will use the option 6 (ignore the error values)

Now, for range H3 if we put a value 64, and hide the third row and use option 5, to ignore the hidden row, the AGGREGATE in Excel we will give the average value for visible numeric values only.

Output without Hiding Row 3

Output after Hiding Row 3

Applying the AGGREGATE formula for other operations, we have

Example – #2

Suppose we have a table for the revenue generated on different dates from the different channels as given below

Now, we want to check the revenue generated for different channels. So, when we apply the sum function we get the total revenue generated but in case if we want to check the revenue generated for Organic channel or direct channel or any other, when we apply filters in excel for the same, the sum function will always give the total sum

We want that when we filter the channel, we get the sum of the values that are visible, so instead of using the SUM function, we will use the AGGREGATE function in order to get the sum of the values that are visible when a filter is applied.

So, replacing the SUM formula with an AGGREGATE function with option code 5 (ignoring the hidden rows and values) we have,

Now, when we will apply the filter for different channels it will show the revenue for that channel only as the rest of the rows gets hidden.

Total Revenue Generated for Direct Channel:

Total Revenue Generated for Organic Channel:

Total Revenue Generated for Paid Channel:

So, we can see the AGGREGATE function calculates the different Sum values for the revenue generated for different channels once they are filtered. So, the AGGREGATE function can be dynamically used for the replacement of different functions for different conditions without using the conditional formula.

Suppose for the same table channel and revenue, some of our revenue values contains an error, now we need to ignore the errors and at the same time, if we want to apply a filter, the AGGREGATE function should ignore the hidden row values also.

When we use option 5, we get the error for the SUM of the total revenue, now in order to ignore the errors we have to use option 6

Using option 6 we get the sum ignoring the error values, but when we apply the filter, for example, filter by channel value Direct we get the same sum ignoring the errors but at the same time we have to ignore the hidden values also.

So, in this case, we will use the option 7 that ignores the error values and at the same time the hidden rows

Things to Remember

The AGGREGATE function doesn’t recognize the function _ num value greater than 19 or less than 1 and similarly for Option number it doesn’t recognize the values greater than 7 and less than 1, if we provide any other values it gives a #VALUE! Error
It always accepts the numeric value and always returns a numeric value as an output
The AGGREGATE in Excel has a limitation; it only ignores the hidden rows but does not ignore the hidden columns.