AGGREGATE Function

The AGGREGATE function returns the aggregate of values in a list, table or cell range.
The tooltip for this function can be seen below and looks quite complicated.
In fact before we try and understand this function we need to make a small modification to the tooltip.

The reference syntax for this function is the one used in 99% of cases, so lets assume it's the only one that exists.
Having the other one displayed in the tooltip is unnecessary and causes users a lot of confusion.

Include Everything

If you want to include hidden rows, error values and nested functions the set "Options" = 4.
AGGREGATE(2,4,range)

Exclude Hidden Rows

If you want to only exclude hidden rows then set "Options" = 5
Calculations that ignore hidden rows.

AGGREGATE(2,5,range)

Exclude Error Values

If you want to only exclude error values then set "Options" = 6
AGGREGATE(2,6,range)

Exclude Nested Functions

If you want to only exclude nested AGGREGATE or nested SUBTOTAL functions then set "Options" = 0.
AGGREGATE(2,0,range)

Automatic AGGREGATES

Not currently possible
This is possible with the SUBTOTAL function. For more details refer to the Subtotals page.

Columns of Data

This function is designed for columns of data and not rows of data.
If your data is going across the page (instead of vertically) hiding a column will have no effect.

Array Form

The tooltip for this function indicates that there is another form to this function.

This function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation.

Important

There are a number of different ways that rows can be hidden: AutoFilter, Manually Hidden and Grouping.