How to use the AGGREGATE function in Excel

Basic formulas in Excel such as SUM, MAX, COUNT and so on are incredibly useful, however do have their limitations especially when working with potentially messy datasets.

This is where the powerful Maths and Trig AGGREGATE function in Excel can come in super handy. In this simple guide we are going to run through the benefits of this function, and how it can be used. We will cover:

• What are the benefits of the AGGREGATE function in Excel?
• The formula
• Examples

Let’s start by talking through some examples of why we would want to use this.

The benefits of AGGREGATE

Let’s say we have a basic dataset and all we want to do is quickly and easy sum all of the numbers together. Normally we would just use the SUM formula and highlight all of the cells.

But what if our dataset isn’t just numbers? Looking at the below example, we have some cells with letters, and error values. In this case the result of a SUM formula gives us #VALUE!

This is where AGGREGATE comes in. The way this function works is it allows us to select from a list of existing functions, including SUM, and apply some additional rules – such as ignore error values. If we were to use AGGREGATE on the above sample set we would get the result of only summing the actual numbers, and ignoring everything else. Super handy!

Let’s move on to the formula itself, and the various features.

The AGGREGATE formula

The syntax for this one can vary a little bit depending on the data, and the function being used. Essentially it has two key variations which are as follows:

=AGGREGATE(function_num, options, ref1, ref2, etc)

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

Before we explain function and options, let’s run through the ref vs array options.

Ref1, Ref2 and so on simply refers to the set of data we are wanting to look at. In our above sample set we would just have Ref1 as the range of those cells. We can continue adding more and more ranges of data if we wanted to, but everything from Ref2 onwards is purely optional.

The Array option is for when we are working with an array of data, or using the array formula. The optional [k] value is a second value that is entirely dependant on the function being used and not always needed. The second table below breaks out which options require [k].

Moving back to the initial two inputs. Function_num is a number from 1 to 19 which selects the function to be called (for example SUM), while option is a number from 0 to 7 to apply a rule to our formula.

As you start typing this formula a pop up appears listing all of the options, but a summary of them is below:

Function_num

Options

Now that we have covered the formula itself, let’s move on to an example.

Examples of using AGGREGATE

Let’s go back to our simple example that we started with, where we were trying to sum a range of values that contained text and errors.

This can very easily be solved by using the SUM function_num, along with option 6 for ignoring error values. The formula would then appear like this:

=AGGREGATE(9,6,B3:B11)

This then provides us with an output that is the sum of the values from B3, down to B11, but completely ignores the letter h in row 6, and the error value in row 10:

Let’s take this one step further by hiding one of the rows. If we hide row 8, where the value in our column is 52333, the output of the above wouldn’t change at all, however if we were to change from option 6 to option 7 which adds an extra layer of ignoring hidden rows, we are now left with a much smaller value as the result:

As we can see, this function is very powerful even for more basic functions, and can save a lot of work when working with spreadsheets in Excel that have a lot of messy data without needing to sift through lots of cells with filters to remove any of these errors or incompatible values.

This sums up our guide on using the AGGREGATE function in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.