Using the New AGGREGATE Function One of the new worksheet functions introduced in Excel 2010 is AGGREGATE. This is a multipurpose function that you can use to sum values, calculate an average, count entries, and more. What makes this function most useful is that it can (optionally) ignore data in hidden rows and error values. Remember that AGGREGATE works only with Excel 2010. If a workbook that uses this function is opened in a previous version of Excel, the formula will display an error.
AGGREGATE arguments This function takes three arguments:
Function_num - A number that describes the summary function Option - A number that describes which options to use Ref - A range reference for the values to be aggregated
The first argument for the AGGREGATE function is a value between 1 and 19 that determines the calculation type. The calculation type, in essence, is one of Excel's other functions. The following table contains a list of these values, with the function it mimics. Function_num
Summary Function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
AVERAGE COUNT COUNTA MAX MIN PRODUCT STDEV.S STDEV.P SUM VAR.S VAR.P MEDIAN MODE.SNGL LARGE SMALL PERCENTILE.INC QUARTILE.INC PERCENTILE.EXC QUARTILE.EXC
978-0-470-47536-2
The second argument for the AGGREGATE function is an integer between 0 and 7 that specifies how hidden cells and errors are handled. The following table summarizes these options. Option
Behavior
0 or omitted
4
Ignore nested SUBTOTAL and AGGREGATE functions Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions Ignore error values, nested SUBTOTAL and AGGREGATE functions Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions Ignore nothing
5
Ignore hidden rows
6
Ignore error values
7
Ignore hidden rows and error values
1 2 3
The third argument of the AGGREGGATE function is a range reference for the data to be aggregated. Note: The SUBTOTAL function always ignores data that is hidden, but only if the hiding is a result of autofiltering or contracting an outline. The AGGREGATE function works similarly, but also ignores data in rows that have 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. At this point, you're probably thinking that you can never remember those code values. Fortunately, when you enter a formula that uses the AGGREGATE function, Excel helps out by displaying descriptions of the first two arguments.
An AGGREGATE example For most users, AGGREGATE will be most useful for creating formulas that ignore error cells. Say you have a range of data, A1:A100, that must be summed—but some of the cells contain error values. If you use the SUM function to add the values the formula returns an error value. You can use the AGGREGATE function to display the result, ignoring the error cells: =AGGREGATE(9,6,A1:A100) The first argument (9) specifies the SUM function, the second argument (6) is the code to ignore errors, and the third argument is the range to be summed.
About the Author: John Walkenbach has written hundreds of articles and created the award-winning Power Utility Pak. His 50-plus books include the bestselling Excel Bible as well as several titles in the Mr. Spreadsheet’s Bookshelf series. Visit his popular Spreadsheet Page at spreadsheetpage.com.
978-0-470-47536-2 Wiley and the Wiley logo are registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries.