AGGREGATE Function || Microsoft Excel

Hi all, We hope all of you are doing great and understanding and honing Excel tips and traps that are shared here.Today, we will talk about What is AGGREGATE Function and How to utilize it in Excel.After going through this article, you would have the capacity to actualize AGGREGATE Function in your spreadsheet all alone, and you would learn what AGGREGATE Function do and how. Let’s discuss AGGREGATE Function in subtle elements below.

Image result for Aggregate function in excel

What is AGGREGATE Function?

Aggregate Function is a very useful tool that helps in performing a specified operation on a given list or database values.It can almost perform twenty different operations on values in aggregation.It ignores hidden rows or error values.

Syntax for AGGREGATE Function:

=AGGREGATE(function_num, options, ref1, [ref2], …)

ARGUMENTS:

  • function_num: It defines the function ID or we can say function number that we use inside AGGREGATE Function to perform the operation on the given list or database values. It is a required parameter.Here is a list of func_num corresponding respective function name,
Function_num

Function

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV.S
8 STDEV.P
9 SUM
10 VAR.S
11 VAR.P
12 MEDIAN
13 MODE.SNGL
14 LARGE
15 SMALL
16 PERCENTILE.INC
17 QUARTILE.INC
18 PERCENTILE.EXC
19 QUARTILE.EXC
  • options: It is also an essential parameter of the AGGREGATE Function, wherein we specify the numeric value that defines which value to ignore during calculation.
Option Behavior
0 or omitted Disgard nested SUBTOTAL and AGGREGATE functions
1 Ignore hidden rows, nestedor settled SUBTOTAL, and AGGREGATE functions
2 Ignore error values, nested SUBTOTAL and AGGREGATE functions
3 Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error value
  • ref1: It is the array or set of values on which we want to perform the Aggregate operation.It is also a required parameter.
  • [ref2…]: This is an optional argument, wherein we specify the numeric value or array of numeric values.

How to use AGGREGATE Function?

Let’s do an example to understand the AGGREGATE Function practically, Suppose we have used SUM Function to sum a range of values and it returns

Suppose we have used SUM Function to sum a range of values from A1 to A7 and it returns the error.

Without Using the Aggregate Function

Let’s SUM the same values using AGGREGATE Function,

Aggregate Function in Excel

And see the difference it gives 13, summing up the values and ignoring all the error values, as we have specified in our AGGREGATE Function to ignore all the error values using ‘6’ as the argument for ‘option’ argument of AGGREGATE Function.

This is how we can use AGGREGATE Function in Excel Sheet.It is very easy to use and yet so helpful, we can use it in different ways with the different set of function type and option type. You can also share this article on Social media platforms with your dears, to help them out with Excel Concepts.If you have any queries regarding AGGREGATE Function, Kindly post it to us and we would try to help you out as soon as possible.

Add a Comment

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