How to use Aggregate Function in Excel

Aggregate Function_Excel_Course_in_Hindi

The Aggregate function in Excel allows you to apply functions like SUM, AVERAGE, COUNT, MIN, MAX, etc. Excel functions like Sum, Average, Count, Min, Max cannot function if the range includes an error. However, if you use the Aggregate function along with these functions, your problem will be fixed.

The Aggregate function is an in-built function in Excel. It is categorized under the Math/Trig Function. It can be used as an aggregate function in Excel. And can be entered as a part of the formula.

At glance, the Aggregate function will look like the SUBTOTAL function. This function is much more powerful. The Aggregate function has two Syntax forms:

1. Reference Form
2. Array Form

1. Reference Form

The syntax of the Aggregate function with Reference Form is

where,

Function_num: You can specify any number between 1 to 13. Each number represents a function. The list of this of the function number along with the name as explained in the image below. This argument is compulsory.

Aggregate Function in Excel
Excel Aggregate function list

Options: This argument specifies which value to be ignored in the evaluation. There is a specific number assigned to each value that is to be ignored. The image below explains the options available.

Aggregate Function in Excel 2
Excel Aggregate function Options

Ref1: This argument is also compulsorily required while entering the formula. This argument specifies for which range you want to use the Aggregate function.

2. Array Form

The syntax of Aggregate function with the Array Form is:

Function_num: You can specify any number between 14 to 19. Each number represents a function. The list of this function number along with the name is explained in the image below. This argument is compulsory.

Aggregate Function - Array Form
Excel Aggregate function Array form list

Options: This argument specifies which value is to be ignored in the evaluation. There is a specific number assigned to each value that is to be ignored. The image below explains to you the various options available.

Aggregate Function in Excel 2
Excel Aggregate function Options

Array: This argument too is compulsory. It works as a reference to a range of cells for which you want to use the aggregate function.

K: Each function in this form is required with a second argument. This argument is compulsory to enter while using an array function. Let’s take a look at how you can use the aggregate function in Excel. Below is the example and how the aggregate function is used along with the other functions.

In our example, let’s take into consideration the Sum function to explain the Aggregate function in Excel using The Reference formula.

Aggregate Function - Example
Excel Aggregate function Example

We want to calculate the Aggregate function for the above example using the reference formula.

Here the arguments will be:

Function_Num: We will enter number 9 which represents Sum.

Options: We will Ignore the Error value so we will enter number 6.

Ref1: This will be the range for which you want to calculate aggregate functions. Here it is A1:A9.

Aggregate Function - Example 01
Excel Aggregate function Example 1

The result will be 323. Let us take one more example that will explain to you how to calculate Aggregate function using the Array form.

We will use the aggregate functions to calculate the small second smallest number in the example.

Aggregate Function - Example
Excel Aggregate function Example

The arguments will be entered as

Function_name: 15 which is a representative of Small.

Options: Ignore Error Value which will be 6

Array: The range for which we want to calculate an aggregate formula. Here the range is A1:A9

K: This is the second argument. Here we want to calculate the smallest number or we will enter 2.

Aggregate Function - Example 02
Excel Aggregate function Example 2

The result will be 23.

You can use the other function as well to calculate the Aggregate functions in Excel. You will now agree with the fact that the Aggregate function is like the Subtotal function in Excel. But it is more powerful.