AGGREGATE Function in Excel 2007

by

AGGREGATE function was officially introduced with Office 2010, but thanks to MFU you can now run it on your 2007 version. It works exactly as its original MS counterpart.

Description

The AGGREGATE function returns the aggregate calculation (such as SUM, AVERAGE, MAX, MIN, etc.) from a list or database, with the option to ignore hidden rows and error values.

It’s more powerful than SUBTOTAL because it offers more calculation options and the ability to ignore errors, making it ideal for working with datasets that contain #N/A, #DIV/0!, or other error values.

Syntax

=AGGREGATE(function_num, options, ref1, [ref2], ...)
ArgumentDescription
function_numA number from 1 to 19 specifying the calculation to perform (see table below)
optionsA number from 0 to 7 specifying which values to ignore (see table below)
ref1The first range or array to aggregate
ref2Optional. Additional ranges or arrays

Function Numbers

NumberFunction
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9SUM
10VAR.S
11VAR.P
12MEDIAN
13MODE.SNGL
14LARGE
15SMALL
16PERCENTILE.INC
17QUARTILE.INC
18PERCENTILE.EXC
19QUARTILE.EXC

Options

NumberBehavior
0Ignore nested SUBTOTAL and AGGREGATE functions
1Ignore hidden rows
2Ignore error values
3Ignore hidden rows and error values
4Ignore nothing
5Ignore hidden rows
6Ignore error values
7Ignore hidden rows and error values

Examples

Basic SUM ignoring errors:

=AGGREGATE(9,6,I2:I17)

Returns the SUM of values in I2:I17, ignoring any error values.

2007-aggregate-sum-ignore-errors

AVERAGE ignoring hidden rows:

=AGGREGATE(1,5,B2:B100)

Returns the AVERAGE of visible cells only in B2:B100.

MAX while ignoring errors and hidden rows:

=AGGREGATE(4,7,C2:C100)

Returns the MAX value in C2:C100, ignoring both errors and hidden rows.

Notes

  • ℹ️ The function is not fully tested yet

See Also

  • MAXIFS & MINIFS
  • MAXIF & MINIF

Leave a comment