AGGREGATE Function

The AGGREGATE function returns the aggregate of values in a list, table or cell range.
The reference syntax for this function is the one usually used, so lets assume it's the only one that exists.
Having two displayed in the tooltip causes users a lot of confusion.

Options

0 = Ignore nested SUBTOTAL and nested AGGREGATE functions (default)
1 = Ignore hidden rows and nested SUBTOTAL and nested AGGREGATE functions
2 = Ignore error values and nested SUBTOTAL and nested AGGREGATE functions
3 = Ignore hidden rows, error values and nested SUBTOTAL and nested AGGREGATE functions
4 = Ignore nothing (include everything)
5 = Ignore hidden rows (from Filtering, Grouping and Manual Formatting)
6 = Ignore error values
7 = Ignore hidden rows and error values

Hidden Rows - By Filtering

Rows can be hidden by applying an AutoFilter (Data, Sort and Filter > Filter)
Hidden rows which are the result of Filtering are always excluded.

Lets filter the table using the "Category" column to display only the rows which have "Food" in column "B".

When using "options" = 1, 3, 5 or 7, the results are similar to the SUBTOTAL function with 109.

Hidden Rows - Manual Formatting

Rows can be Manually Hidden using (Home, Format > Hide & Unhide > Hide Rows).

What happens when we manually hide row 5.

When using "options" = 1, 3, 5 or 7, the results are similar to the SUBTOTAL function with 109.

Hidden Rows - By Grouping

Rows can be hidden as a result of a Grouping (Data tab, Outline > Group).

What happens when we collapse the Food group.

When using "options" = 1, 3, 5 or 7, the results are similar to the SUBTOTAL function with 109.

Nested AGGREGATEs

For more details refer to the Nested Aggregates

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.

Other

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.
It is possible to insert Automatic Subtotals using the SUBTOTAL function. This is not possible with the AGGREGATE function.