SUM is probably the most frequently used function in Excel. And probably the first function we learned. Though powerful, it cannot handle errors… 😰

This is the fact we need to deal with. SUM cannot handle errors. AGGREGATE comes to rescue.

AGGREGATE was introduced in Excel 2010. I would say AGGREGATE is the enhanced version of SUBTOTAL. After more than a decade, few people know how to use AGGREGATE, at least to the people I know. 😅

What AGGREGATE does?

AGGREGATE is the function to perform common aggregate functions a range of values (array or references). It can ignore hidden rows, subtotals, errors in the list.

To understand how it works, we need to know the syntax (indeed this is appliable to all functions).

The syntax

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

where
function_num is the aggregation (e.g. sum, count, average, etc.) we want
options is how we want to ignore certain values (e.g. hidden rows, errors, etc.)
array is the range of values (note: this function is designed for vertical array, i.e. column.  Please read the special note at the end of the post)

More details can be found here.

Indeed, there are nineteen function_nums and eight options for selection. Don’t try to memorize them because we don’t have to.

Writing the function

When we start writing the formula, the moment we input “=AGGREGATE(“, we will see a list of choices for the first argument. We may select from the list by either down arrow key, followed by Tab, or inputting the number code directly or even using mouse click).

In our example we want to perform SUM, hence 9 is input. The moment I input 9 and a comma, I step into the second argument of the function, and I see another list of choices for that argument. Isn’t it cool?

In our example, we want to ignore errors. Thus, we will input 6, followed by the final required argument, the array i.e. A2:A10 in our example.

Here’s the final formula to sum range with errors.

=AGGREGATE(9,6,A2:A10)

As simple as this!

You may download a sample file here to explore different options of ignoring values.

During your experiment, you may find the following note relevant:

The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.

Msft Support

Once again, don’t miss the “Note” or “Important” when reading documents. They are usually gems.

Enjoy! 😉