AGGREGATE

Using the Excel AGGREGATE Function

Summary

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).

Syntax

Reference Form

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

Array Form

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


Syntax Breakdown

Function_num
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

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
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
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.

Errors
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.

Excel AGGREGATE VALUE Error