Aggregate

 

Excel functions such as SUM, COUNT, LARGE and MAX don’t work if a range includes errors. However, you can easily use the AGGREGATE function to fix this.

1. For example, Excel returns an error if you use the SUM function to sum a range with errors.

Without Using the Aggregate Function

2. Use the AGGREGATE function to sum a range with errors.

Aggregate Function in Excel

Explanation: the first argument (9) tells Excel that you want to use the SUM function. The second argument (6) tells Excel that you want to ignore error values.

3. It’s not easy to remember which argument belongs to which function. Fortunately, the AutoComplete feature in Excel helps you with this.

AutoComplete Feature

4. The AGGREGATE function below finds the second largest number in a range with errors.

Aggregate and Large Function

Explanation: the first argument (14) tells Excel that you want to use the LARGE function. The second argument (6) tells Excel that you want to ignore error values. The fourth argument (2) tells Excel that you want to find the second largest number.

5. The AGGREGATE function below finds the maximum value ignoring error values and hidden rows.

Ignore Error Values and Hidden Rows

Explanation: the first argument (4) tells Excel that you want to use the MAX function. The second argument (7) tells Excel that you want to ignore error values and hidden rows. In this example, row 2 is hidden.