AGGREGATE Excel Function

Excel AGGREGATE Function (Table of Contents)

• AGGREGATE In Excel
• AGGREGATE Excel Formula
• How to Use AGGREGATE Function in Excel?

AGGREGATE In Excel

AGGREGATE in excel is categorized as Math/Trig Function was introduced in Excel 2010 and it performs specified operation and returns an AGGREGATE in a list or database. AGGREGATE Function in Excel allows us to use functions like count, average, sum, max or min by ignoring the errors and the hidden rows.

AGGREGATE Excel Formula

Explanation of AGGREGATE in Excel

There are two syntax 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 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.

 

How to Use AGGREGATE Function in Excel?

Let’s look below at some of the examples of AGGREGATE function on Excel. These AGGREGATE Function in excel examples will help you in exploring the use of the AGGREGATE function in Excel.

AGGREGATE in Excel Example – #1

Suppose we have a list of numbers and we will compute the Average, Count that is the number of cells that contains 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 selected 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, 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 the 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 the 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

AGGREGATE in Excel 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 filter 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 in excel in order to get the sum of the values that are visible when a filter is applied.

So, replacing the SUM formula with AGGREGATE function in excel 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 in excel computes the different Sum values for the revenue generated for different channel once they are filtered. So, the AGGREGATE function in excel can be dynamically used for the replacement of different functions for different conditions without using the conditional formula.

Suppose for same table channel and revenue, some of our revenue values contains error, now we need to ignore the errors and at the same time if we want to apply a filter, the AGGREGATE in Excel 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 About AGGREGATE Function in Excel

• The AGGREGATE Function in Excel 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.

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

Recommended Articles

This has been a guide to 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 –

• How to use ROW Excel Function?
• MAX Excel Function
• How to use REPLACE Excel Function
• How to use POWER Function in Excel
• How to use IF Excel Function
• How to use True Excel Function