AGGREGATE
functionPro Tip: use AGGREGATE
when you want your statistics (e.g. MIN
,SUM
) to ignore cells with errors or hidden cells.
The AGGREGATE
function allows for performing statistical aggregations (hence the name) with more customization than the standard MIN
, MAX
, etc. functions. The AGGREGATE
function is also odd in that it uses a set of magic numbers (or constants) as parameters for what aggregate to complete as well as any options that will be applied. Where the AGGREGATE
function shines above the stocks functions is that it allows you to handle: cells with errors and hidden cells. With the power to consider those options comes the need to be very explicit about the calculation you want to use. For error handling, it can be quite frustrating to run an AVERAGE
of 100k cells to find out that 2-3 of them have random errors of no consequence. You can specify the ignore errors
option to have AGGREGATE
disregard them. For hidden cells, you are most likely only going to use that in the context of filtering. You set some filters and then use AGGREGATE
to only get the SUM
of the visible cells. This allows you to specify any filters you want manually (as opposed to a SUMIF
or an array formula) and then have a single formula which can give the aggregations you want.
For excluding hidden cells, there is not an alternative way to handle that without using VBA. For ignoring errors, the alternative is often an array formula, helper column, or possibly a SUMIF
or other IF
function.