Excel AGGREGATE Function

Using the Excel AGGREGATE Function


Excel's AGGREGATE function returns an aggregate in a list or database and can apply different functions, such as AVERAGE and COUNT, with the option to ignore hidden rows or error values. A total of 19 operations are available (discussed below).


Reference Form

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

Array Form

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

Syntax Breakdown

The function_num argument is required. This is a number from 1 to 19 that specifies which function to use. The options are as follows:

Excel AGGREGATE Function_Num Options

The options argument is required. This is a numerical value that specifies which value to ignore in the evaluation range for the function. The options are as follows:

Excel AGGREGATE Option

Ref1 is required. This is the first numerical argument used by functions that handle multiple numeric arguments and for which you want the aggregate.

Ref2 and subsequent Ref# arguments are optional. The function can handle up to 253 ref arguments.

Some arguments, shown below, will require a ref2 argument. When a function receive an array, ref1 is an array, an array formula, or a reference to a range of cells for which you are trying to find the aggregate value.

Excel AGGREGATE Ref2 Required

Usage Notes

AGGREGATE is used to return an aggregate calculation with the option of ignoring hidden rows and errors. Users should note that AGGREGATE doesn't work with 3D references and is designed for vertical, not horizontal, ranges.

The function will return the #VALUE! error if a second function argument is expected, but not supplied. For example, in the image below the SMALL function (function_num = 15) is required to have a ref2 argument. Since it is not supplied the function returns an error.