AGGREGATE Excel Function

AGGREGATE Function in Excel

The AGGREGATE function of Excel returns the aggregate of a given data table or data list. The first argument is a function number, and the further arguments consist of a range of data sets. One must remember the function number to know which function to use.

Syntax of the AGGREGATE Function

Aggregate formula in Excel 1

The two syntaxes of the function are stated as follows:

Array Syntax

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

Reference Syntax

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

The function accepts the following arguments:

Function_num: This number represents a specific function that is to be used. It ranges from 1-19.

Aggregate Function (Function num)

Options: This is a numeric value ranging from 0 to 7. It determines the values that are ignored during calculations.

Aggregate Function Option)

Ref1, ref2, ref[3]: This is the numeric value or values on which computations are to be performed.

It is mandatory to provide at least two of these arguments, and the remaining arguments are optional.

The Characteristics of the AGGREGATE Function

The features of the AGGREGATE function are listed as follows:

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

Examples

Example #1

We have a list of numbers, and we want to compute the Average, Count (the number of cells that contain a value), CountaCountaThe COUNTA function is an inbuilt statistical excel function that counts non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but cell A2 is empty. Therefore, the formula “=COUNTA(A1, A2, A3)” returns 2.read more (count of cells that are not empty), Maximum, Minimum, Product, and Sum. The values are given in the following table:

Aggregate Excel Function Example 1
Aggregate Excel Function Example 1.1

Let us first calculate the Average in the ninth row for all the given values. For Average, the function_ num is 1.

Aggregate Excel Function Example 1-2

The values are given in column C. Since no values are ignored, we select option 4 (i.e., ignore nothing).

Aggregate Excel Function Example 1-3

We select the range of values C1:C8.

Aggregate Excel Function Example 1-4

We calculate the Average by omitting the value of “k.”

The average value is 5.25, as shown in the succeeding image.

Aggregate Excel Function Example 1-5

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

In range E1:E8, cell E6 contains an error value. If we use the same AGGREGATE formula, we get an error. If an appropriate option is used, the AGGREGATE function gives the average of the remaining values while neglecting the error in E6.

To ignore the error values, we select option 6.

Aggregate Example 1-6
Aggregate Example 1-7

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

Aggregate Example 1-8

For cell H3, we enter the value 64, hide the third row, and use option 5 to ignore the hidden row. The AGGREGATE function gives the average value of the numeric values that are visible.

The output without hiding the third row is shown in the succeeding image.

Aggregate Example 1-9

The output after hiding the third row is shown in the succeeding image.

Aggregate Example 1-10

We apply the AGGREGATE formula to other operations, as shown in the following image.

Aggregate Example 1-11

Example #2

A table shows the revenueRevenueRevenue is the amount of money that a business can earn in its normal course of business by selling its goods and services. In the case of the federal government, it refers to the total amount of income generated from taxes, which remains unfiltered from any deductions.read more generated on different dates from the different channels. We want to find the revenue generated from the different channels.

Aggregate Excel Function Example 2

We apply the SUM functionSUM FunctionThe SUM function in excel adds the numerical values in a range of cells. It is categorized under the math and trigonometry function entered by typing “=SUM” followed by the values to be summed. The values can be numbers, cell references or ranges.read more to get the total revenue generated.

Aggregate Excel Function Example 2-1

If we want to check the revenue generated from “Organic Channel” or “Direct Channel,” we apply filters in Excel. The SUM function gives the total sum.

Aggregate Excel Function Example 2-2

To get the sum of the visible values when a filter is applied, instead of using the SUM function, we will use the AGGREGATE function. So, on replacing the SUM formula with an AGGREGATE function with option code 5 (ignoring the hidden rows and values); we have,

Aggregate Example 2-3

We apply the filter to different channels. It shows the sum of revenue for the filtered channel. The rest of the rows are hidden.

Total Revenue Generated for Direct Channel is shown in the succeeding image:

Total Revenue Generated for Direct Channel

Total Revenue Generated for Organic Channel is shown in the succeeding image:

Total Revenue Generated for Organic Channel

Total Revenue Generated for Paid Channel is shown in the succeeding image:

Total Revenue Generated for Paid Channel

The AGGREGATE function calculates the different SUM values for the revenue generated from different channels. The AGGREGATE function is dynamic in nature. It is used to replace different functions for different conditions without using the conditional formula.

Let us assume that in the same table, some of the revenue values contain an error. We want to ignore the errors. At the same time, if we apply a filter, the AGGREGATE function should ignore the hidden row values.

Aggregate Example 2-7

When we use option 5, we get an error as the sum of the total revenue. With the usage of option 6, the SUM function ignores error values.

error for the SUM of the total revenue

When we apply the filter (for example, filter by channel value Direct), the SUM function ignores the errors. However, we also need to ignore the hidden values.

sum ignoring the error values

In this case, we use option 7. This ignores the error values and the hidden rows simultaneously.

option 7 that ignores the error values

Frequently Asked Question (FAQS)

What is an AGGREGATE function in Excel?

The AGGREGATE function in Excel allows applying different aggregate functions like AVERAGE, SUM, PRODUCT, COUNT, COUNTA, MAX, or MIN to a list of data, with an option to ignore hidden rows and error values.

How does the AGGREGATE Excel function work?

An AGGREGATE function in Excel does a calculation on a set of values and returns a single value. Except for COUNT, the AGGREGATE functions ignore null values.

When should the AGGREGATE Function of Excel be used?

An AGGREGATE function, a built-in function in Excel, is categorized as a Math or Trig Function. It finds its usage as a worksheet function (WS) in Excel. As a WS function, the AGGREGATE function can be entered as part of a formula in a cell of a worksheet. Microsoft created it to address the limitations of conditional formatting.

Key Takeaways
  • AGGREGATE function in Excel returns the aggregate of data provided in a table or data list.
  • In the AGGREGATE function, the first argument is function number, and further arguments are for a range of the data sets.
  • The reference syntax of the AGGREGATE formula is “=AGGREGATE(function_num, options, ref1, ref2, ref[3],…)”
  • The Array syntax of AGGREGATE formula is “=AGGREGATE(function_num,options,array,[k])”
  • “k” is an optional argument used with functions like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC, and QUARTILE.EXC.
  • The AGGREGATE function is often used to replace different functions for different conditions without using the conditional formula.

AGGREGATE Excel Function Video

Recommended Articles

Guide to the AGGREGATE function in Excel. Here, we discuss how to use AGGREGATE formula along with step by step example. You may also look at these useful functions in Excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *