Chatting about Excel and More

AGGREGATE()

Excel 2010 introduced a couple of new functions. If you currently use the Subtotal() feature, you might be interested in a new function that was introduced in Excel 2010 - AGGREGATE().


The AGGREGATE() function is similar to the SUBTOTAL() function; however, it gives you greater control over what to ignore or what to include. It is particularly handy if you routinely have error messages in data that you are trying to manipulate. You can specify that you want the calculation to ignore hidden rows, subtotals, eror cells and or any combination of the the 3.

The syntax is =AGGREGATE(function_num,options, array, [k])

The function number, like Subtotal() 's , is a number that refers to the function you want to perform. Aggregate's function number has been expanded to 19.

Options allows you to specify what you want to ignore or exclude:

Array are the cells that you are trying to aggregate/subtotal.

Below is a simple example. For Java Joe's I have filtered all the data to just see customer "Cup of Joe"'s sales.

Now, I just want to add up Cup of Joe's Total Sales in Column F.

If I tried to use the SUBTOTAL() function I would get an error message since F13 has an error value.

 

Instead, I can use AGGREGATE() and tell it to ignore any error values as well as hidden rows. The answer of 1053.2 is returned.