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