Microsoft Office Tutorials and References

In Depth Information

**Using AGGREGATE to Ignore Error Cells or Filtered Rows**

Figure 11.3.

Figure 11.3.
After you click AutoSum, the total formulas are automatically

After you click AutoSum, the total formulas are automatically

entered.

entered.

Using

Using
AGGREGATE

AGGREGATE
to Ignore Error Cells or Filtered Rows

to Ignore Error Cells or Filtered Rows

Added in Excel 2010, the AGGREGATE function lets you perform 17 functions

on a range of data while selectively ignoring error cells and/or rows hidden

by a filter.

Syntax

=AGGREGATE(function_num, options, array, [k])

The optionsargument is the interesting feature of the new function. You can

choose to ignore any, all, or none of these categories:

•
Error values

•
Hidden rows

•
Other SUBTOTAL and AGGREGATE functions

On one hand, the capability to ignore filtered rows and other AGGREGATE

functions is similar to the SUBTOTAL function. The capability of

AGGREGATE to ignore error values solves a common Excel problem. For most

Excel functions, a single #N/A error cell in a range causes most functions to