How to use AGGREGATE function in Excel?

The AGGREGATE function in Excel is a versatile function that can perform various operations, such as AVERAGE, SUM, COUNT, MAX, MIN, and others, while also allowing you to ignore hidden rows, errors, or nested subtotals. It’s particularly useful when you need to perform calculations on data that may include errors or when you have filtered data.

Here’s a step-by-step guide on how to use the AGGREGATE function:

Syntax

AGGREGATE(function_num, options, array, [k])

function_num: This specifies which function to use. It can be a number from 1 to 19, each corresponding to a specific function. For example, 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, etc.
options: This determines which values to ignore. It can be a number from 0 to 7:

0: No values are ignored.
1: Ignores SUBTOTAL and AGGREGATE functions.
2: Ignores hidden rows, errors, SUBTOTAL, and AGGREGATE functions.
3: Ignores hidden rows.
4: Ignores errors.
5: Ignores hidden rows and errors.
6: Ignores hidden rows, SUBTOTAL, and AGGREGATE functions.
7: Ignores errors, SUBTOTAL, and AGGREGATE functions.

array: This is the range of cells on which you want to perform the calculation.
[k]: This is an optional argument needed for certain functions like LARGE, SMALL, PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC, and QUARTILE.EXC that require a ranking or percentile argument.

Example

   =AGGREGATE(1, 6, A1:A10)

AVERAGE ignoring errors:

This formula calculates the average of the range A1:A10 while ignoring any hidden rows and errors within that range.

   =AGGREGATE(9, 3, A1:A10)

SUM ignoring hidden rows:

This formula sums up the values in the range A1:A10, ignoring hidden rows.

   =AGGREGATE(14, 4, A1:A10, 2)

LARGE value calculation:

This formula returns the second-largest number in the range A1:A10, ignoring any errors.

Tips

The AGGREGATE function is typically used when your data set contains errors or includes hidden rows that you don’t want to consider in your calculations.
It’s powerful for summarizing data in tables that use Excel’s filtering options.

By leveraging the different options for ignoring certain types of data, AGGREGATE can provide more robust calculations in complex datasets.

facebooklinkedin