Microsoft Office Tutorials and References
In Depth Information
Tip 45: Using the AGGREGATE Function
The second argument for the AGGREGATE function is an integer between 0 and 7 that specifies how
hidden cells and errors are handled. Table 45-2 summarizes these options.
Table 45-2: Values for the Second Argument of the AGGREGATE Function
0 or omitted
Ignore nested SUBTOTAL and AGGREGATE functions.
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions.
Ignore error values, nested SUBTOTAL and AGGREGATE functions.
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.
Ignore hidden rows.
Ignore error values.
Ignore hidden rows and error values.
The third argument of the AGGREGGATE function is a range reference for the data to be aggregated.
The SUBTOTAL function always ignores data that is hidden, but only if the hiding is a
result of filtering a table or contracting an outline. The AGGREGATE function works
similarly, but also ignores data in rows that has been hidden manually. Note that this
function does not ignore data in hidden columns. In other words, the AGGREGATE function
was designed to work only with vertical ranges.
Fortunately, Excel provides “formula autocomplete” assistance when you enter this function in a
formula. Figure 45-1 shows the drop-down list of arguments that appears automatically. Choose the
argument and press Tab to continue.
Figure 45-1: Using autocomplete to identify the argument values for AGGREGATE.