Skip to content

Forget SUM(), use Aggregate() in Excel

Aggregate() is the grown up version ‘all in one’ function that can replace common functions like Sum() and Subtotal() plus powerful options not available in those older functions.

AGGREGATE() function is a more advanced alternative to the SUBTOTAL function, offering significantly expanded capabilities. Almost an ‘all in one’ solution instead of using multiple functions.

  • Aggregate() supports 19 different operations, ranging from basic calculations like SUM and AVERAGE to more advanced ones such as PRODUCT and STANDARD DEVIATION.
  • LARGE and SMALL operations can show not just the most extreme values, but also the near-extreme like the second-largest or third-smallest.
  • 8 options for managing errors and hidden data, enabling smooth calculations even when error values are present.
  • Aggregate() can skip over hidden rows, which makes it especially effective for filtered or dynamic data.
  • AGGREGATE can also handle nested AGGREGATE or SUBTOTAL functions, unlike Subtotal()

It’s available in all recent Excel’s including Excel on the web (since at least Excel 2016 for Windows and Excel 2021 for Mac).

The AGGREGATE function comes in two forms:

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

and the array form =AGGREGATE(function_num, options, array, [k])

Calculation type by number

Start Aggregate() by telling it which type of calculation to make.  Each function / calculation has a number but you don’t have to memorise them because a drop-down list appears in the formula bar.

Available Aggregate calculations:

1  = AVERAGE 

2  = COUNT 

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

The options argument Controls how errors and hidden rows are handled during the calculation.  This is incredibly useful in tables with filters, errors and subtotals.

0 – Ignore nested SUBTOTAL and AGGREGATE functions 

1 – Ignore hidden rows, nested 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 values

Other Parameters

Then, tell Aggegrate what cells, range or array to get values from.

Ref1 (required): The primary range of data to which the function will be applied.
Array (required): The set of values or an array formula used in the function.
Ref2 (optional): Additional ranges of cells. You can include up to 252 additional ranges.

And finally

The last parameter is needed with some functions.

[k] (optional): Used with functions like LARGE, SMALL, PERCENTILE, and others that need a ranking or percentile value.

With LARGE (14), the [k] chooses which to select – 1 = Largest 2 = second largest 3 = third largest

With SMALL (15), the [k] chooses which to select – 1 = Smallest 2 = second smallest 3 = third smallest

You don’t need to worry about choosing the correct format—in the formula bar, Excel automatically selects the right one based on the function number (function_num) you specify.

Let’s check out  few examples on the usage of AGGREGATE function in Excel.

Ignore Hidden Rows When Calculating AVERAGE

When using aggregate functions like AVERAGE in Excel, hidden rows are usually included by default in the calculation. However, sometimes you want to ignore hidden rows (i.e., rows hidden manually or by filters). Below is the formula for doing so:

=AGGREGATE(1, 5, B2:B10)

1 signifies  AVERAGE,

5 signifies Ignore hidden rows and

B2:B10 = the range of data.
This will calculate the average of the visible cells in the range, ignoring any rows that are hidden manually (not filtered)

Find the 2nd Largest Value While Ignoring Errors

You can fetch the 2nd largest numeric value from a list, while ignoring any error values (like #DIV/0!, #N/A, etc.).

Formula:

=AGGREGATE(14, 6, B2:B10, 2)

1 signifies  LARGE, 6 = Ignore error values, B2:B10 = Range of data, 2 =Return the 2nd largest value useful when your data might include #DIV/0!, #N/A, or other errors, but you still want to find the 2nd highest value among the valid entries.

Count Numbers Ignoring Subtotals and Hidden Rows

You can  count numbers in a range while ignoring subtotals and hidden rows in Excel, by using the AGGREGATE function

=AGGREGATE(2, 7, B2:B10)

2 signifies COUNT, 7 = Ignore hidden rows and nested SUBTOTAL or AGGREGATE results
This ensures your count only includes visible and valid data, skipping over subtotal rows.

Return the Smallest Value, Ignoring Errors

You can find the smallest numeric value in a range, but ignore any error values like #DIV/0!, #N/A, etc. Simply use the formula:

=AGGREGATE(15, 6, B2:B10, 1)
  1. = SMALL, 6 = Ignore errors, B2:B10 = Data range, 1 = Return the smallest (1st smallest) value.

This is ideal when you’re working with datasets where some values may be invalid due to calculation issues.

Excel gets Regular Expression functions

Eight SUM functions in Excel … and more

About this author

Mayurakshi Mohapatra

Maya has been a contributor to Office Watch since 2017. She's an experienced Technical Writer working in the gaming, entertainment and financial industries. Boasts a Master of Arts (M.A.) in English Language and Literature. After a stay in Sydney, she speaks both English and Australian . ‘Making a difference’ is her motto in life.