How to Use the AGGREGATE Function in Excel
(Note: Suitable for users of Excel 2016, 2019, 2021, and Excel for Microsoft 365.)
Objective
Use the AGGREGATE Function in Excel to return an aggregate calculation, optionally ignoring errors values and hidden rows.
The AGGREGATE Function Explained
The AGGREGATE function in Excel returns an aggregate calculation like SUM, AVERAGE, COUNT, MIN, MAX etc. We can choose to optionally ignore specific things like hidden rows and errors. This means we are still able to perform the calculation despite these things being present in the dataset.
For example, the table below shows some Items and Prices of each of those items. We want to do a SUM calculation to add up the Prices in cell B16. However, we have errors in our data. If we simply attempt to do a SUM calculation, the result will also be an error.
We can use the AGGREGATE function in Excel to perform the SUM calculation and ignore the errors in the cells.
However, this isn’t the only advantage of using AGGREGATE. AGGREGATE can handle many array operations without the need for CSE (Control+Shift+Enter).
The AGGREGATE function in Excel has 4 arguments:
=AGGREGATE(function_num,options,array,[k])
| function_num | presents a list of 19 operations that can be performed by the AGGREGATE function |
| options | presents a list of 7 combinations of items to be ignored in the calculation |
| array | the range of cells we want to aggregate |
| k | (optional) returns the nth largest or smallest value when using the LARGE or SMALL function |
- Example 1: SUM (Ignore errors)
- Example 2: AVERAGE (Ignore hidden rows and errors)
- Example 3: LARGE (Ignore errors, hidden rows and return second largest)
Related reads:
How to Use Excel MATCH Function
How to Graph a Function in Excel
How to Use SUMPRODUCT Function in Excel
Example 1: SUM (Ignore errors)
In this first example, we will use the SUM function to calculate the total price for our list of items. We will choose to ignore errors only.
- Type =AGGREGATE
- Select SUM from the list.
- Select Ignore error values from the list.
- Select the array to aggregate.
The result will be returned and any errors will be ignored.
Example 2: AVERAGE (Ignore hidden rows and errors)
This time, we will find the average price from the list and ignore hidden rows and errors.
- Type =AGGREGATE
- Select AVERAGE from the list.
- Select Ignore hidden rows and error values.
- Select the array to aggregate.
The result will be returned and any errors and hidden rows will be ignored.
Suggested reads:
How to Create an Excel Gauge Chart the Easy Way
Battle of the Excel Lookup Functions: VLOOKUP vs INDEX/MATCH vs XLOOKUP
Example 3: LARGE (Ignore errors, hidden rows and return second largest)
In this example, we need to find the second largest value in the Price column. We need to ignore error values and hidden rows. The LARGE function is similar to MAX but it has an additional [k] argument.
- Type =AGGREGATE
- Select LARGE from the list.
- Select Ignore hidden rows and error values.
- Select the array to aggregate.
The optional [k] argument allows us to specify if we want to return the largest (1), the second largest (2), the third largest (3) etc. We want to return the second largest value.
The result returned is the second largest value in the Price column.
Also read:
How to Password Protect an Excel File
How to Use the Excel IFS Function
Please visit our free resources center for more high-quality Excel and Microsoft Suite application guides.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT taught Excel and other business software for over ten years. You can access 150+ IT training courses for a low monthly fee.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
