AGGREGATE Excel Function

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ramya Puttaparthi Tirumala

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

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.

  • 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,…)”
  • The Array syntax of AGGREGATE formula is “=AGGREGATE(function_num,options,array,)”
  • “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.

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,)”

The following additional arguments are used in array syntax:

  • Array: This is an array of values on which we want to operate.
  • K: This is a numeric value used with functions like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC, and QUARTILE.EXC.

The “array” is a mandatory argument and “k” is an optional argument.

Reference Syntax

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

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: 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.

AGGREGATE Excel Function Video

https://youtu.be/mvgFLKOm5T4

The Characteristics of the AGGREGATE Function

The features of the AGGREGATE function are listed as follows:

  • It does not recognize the function_num value greater than 19 and lesser than 1.
  • For Option Number, the AGGREGATE function does not identify the values greater than 7 and lesser than 1.
  • If we feed any other values, it gives a “#VALUE! error.”
  • It always accepts a numeric value and always returns a numeric value as the output.
  • It ignores only the hidden rows but does not ignore the hidden columns.

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), Counta (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 revenue 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 function 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.

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 –

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