AGGREGATE Function in Excel

AGGREGATE in Excel (Table of Contents)

• AGGREGATE in Excel
• AGGREGATE Formula in Excel
• How to Use AGGREGATE Function?

AGGREGATE in Excel

• The AGGREGATE function was introduced in Microsoft Excel 2010, not available in Excel 2003 or 2007 version
• It acts as a worksheet function & is an inbuilt function in Excel that is categorized under Math/Trig Function
• Excel AGGREGATE has an additional built-in option and function that allows you to ignore hidden rows, errors & nested subtotals, it is more powerful & advanced form compared to others
• It is so versatile, it can replace the below mentioned 19 most commonly used Excel functions in statistics at one go (Mentioned in page 2)

Definition

“Returns the AGGREGATE in a database of values or list”

It means it performs a number of calculations (19 Excel statistical functions)

AGGREGATE Formula in Excel

AGGREGATE function have two type of formula

It can be noticed when you type AGGREGATE function in an excel cell

1) AGGREGATE in Reference form

2) AGGREGATE in Array Form

Function_num: It is a number, it can be from 1 to 19, It depends on which specific function you want to use in the below-mentioned list

Here each number represents a function, it is a compulsory argument

1 to 13 are Reference form and 14 to 19 are array form

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: is the number 0 to 7 that specifies which values to ignore for the aggregate function Note: If the options parameter is omitted, by default options is set to 0

0: Ignore nested SUBTOTAL and AGGREGATE functions

1: Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows

2: Ignore nested SUBTOTAL, AGGREGATE functions, and error values

3: Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows & error values

4: Ignore nothing

5: Ignore hidden rows

6: Ignore error values

7: Ignore hidden rows and error values

ref1, ref2, ref[3]: It is a first numeric argument for the function when using the REFERENCE syntax. it is values or numeric value on which we want to perform the computation, Here minimum two arguments are required, rest arguments are optional, For second reference, Numeric arguments should be between 2 to 253 for which you want the aggregate value

Array: An array or refers to a range of cells when using the ARRAY syntax

[k]: The last 6 functions (under 1 to 19 function list): k value as a fourth argument

It is an optional argument, it is used, if we need to find out LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, or QUARTILE.EXC when using the ARRAY syntax

How to Use the AGGREGATE Function in Excel?

This AGGREGATE function is very simple easy to use. Let us now see how to use the AGGREGATE Function with the help of some examples.

Example #1

The following table contains yearly sales data (2015)

If you run the =SUM (B8:B16) function directly in cell B17,

it gives the correct value because that column does not contain hidden rows, errors & nested subtotals

Here will calculate the SUM using an AGGREGATE function in cell B19.

=AGGREGATE(9,4,B8:B16),

The result will be 487

Function_ num: For SUM function, the function_ num is 9

Option: In Column B, i.e. For 2015-year sales data, all values are given and we won’t have to ignore any values, it does not contain hidden rows, errors & nested subtotals. so, we will select Option 4 (ignore nothing)

Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is B8:B16, it is selected as an array of numeric values

‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. we are calculating the SUM here, so we will omit the value of k. 

Example #2

The following table contains yearly sales data (2016)

In column C, for the 2016 yearly sales data. In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A), In the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the error value.

If you run the =SUM (C8:C16) function directly in cell C17,

it returns an error value, due to an error in that range. because that column contains #DIV/0! & #N/A errors.

In order to ignore the error values, we have to use option 6 in an AGGREGATE function

=AGGREGATE(9,6,C8:C16).

The result or output will be 334

Function_ num: For SUM function, the function_ num is 9

Option: In Column C, i.e. For 2016-year sales data, In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A). To ignore these errors, we will select Option 6 (Ignore error values)

Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is C8:C16, it is selected as an array of numeric values

‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. we are calculating the SUM here, so we will omit the value of k.

when an appropriate option is used in the AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cell C11 & C12. i.e. 334

Example #3

The following table contains yearly sales data (2017)

In column D, for the 2017 yearly sales data. In the range D8:D16, a cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A), In the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the hidden row & error value.

If you run the =SUM (D8:D16) function directly in cell D17,

it returns an #N/A error value, due to an error in that range. because that column contains #N/A error & hidden row or blank value.

=AGGREGATE(9,7,D8:D16),

The result or output will be 262

Function_ num: For SUM function, the function_ num is 9

Option: In column D, for the 2017 yearly sales data. In the range D8:D16, a cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A). To ignore these errors, we will select Option 7 (Ignore hidden rows and error values)

Note: you can hide rows by selecting the row(s) > right-click on specific row > Hide

Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is D8:D16, it is selected as an array of numeric values

‘k’ is an optional argument, is used only for a function like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC. we are calculating the SUM here, so we will omit the value of k.

when an appropriate option is used in an AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cell D9 & D12. i.e. 262

Things to Remember about the AGGREGATE Function

• The AGGREGATE function is applicable only for vertical ranges or columns of data. It is not designed for horizontal range or rows of data.
• It has a limitation; it only ignores the hidden rows, It does not ignore the hidden columns
• An AGGREGATE function is applicable only for the numeric value
• Function _ num argument value should not be greater than 19 or less than 1, similarly for option argument should not be greater than 7, otherwise, it will give #VALUE! error
• In AGGREGATE function, if in function number argument, if you are using 14 to 19 (LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC or QUARTILE.EXC), the “K” argument should be used. g. =AGGREGATE(15, 6, A1:A9, 3). If in case, “K” value or second reference argument is ignored, it will result in a #VALUE! error

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

Recommended Articles

This has been a guide to AGGREGATE Function. Here we discuss the AGGREGATE Formula and how to use AGGREGATE Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

1. Guide to CONCATENATE Function
2. Guide to OR Function
3. Guide to NOT Function
4. Guide to TRANSPOSE Function