AGGREGATE Excel Function

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:

1. Reference Syntax

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

2. 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 during 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 operate; it is used in array syntax of the AGGREGATE function in excel.

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

Examples

Example – #1

Suppose we have a list of numbers, and we will compute the Average, Count that is the number of cells that contain 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 that we will select Option 4 (ignore nothing)

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

Since ‘k’ is an optional argument and is used when a function like LARGE, SMALL in Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC is used, but in this case, we are calculating the Average to 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 use the same AGGREGATE formula, we will get an error. Still, when an appropriate option is used, the AGGREGATE in Excel gives the average of the remaining values neglecting the error value in E6.

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 visible values, so instead of using the SUM function, we will use the AGGREGATE function 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 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:

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 to replace different functions for different conditions without using the conditional formula.

Suppose for the same table, 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. To ignore the errors, we have to use option 6

Using option 6, we get the sum ignoring the error values. Still, 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 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. Similarly, for Option number, it doesn’t identify 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.

AGGREGATE Excel Function Video

Recommended Articles

This has been a guide to the AGGREGATE Function in Excel. Here we discuss the AGGREGATE Formula in excel and how to use AGGREGATE in Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• REPLACE Function in Excel
• POWER Function in Excel
• IF Function
• True Function in Excel