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

Option

Behavior

0 or omitted

Ignore nested SUBTOTAL and AGGREGATE functions.

1

Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions.

2

Ignore error values, nested SUBTOTAL and AGGREGATE functions.

3

Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.

4

Ignore nothing.

5

Ignore hidden rows.

6

Ignore error values.

7

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.

Note

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.