How to use the AGGREGATE function
What is the AGGREGATE function?
The AGGREGATE function allows you to calculate different specific functions to a list or database. A special AGGREGATE function feature is to exclude hidden rows and error values in calculations.
Table of Contents
- Introduction
- Syntax
- Example 1 - AVERAGE
- Example 2 - COUNT
- Example 3 - COUNTA
- Example 4 - MAX
- Example 5 - MIN
- Example 6 - PRODUCT
- Example 7 - SUM
- What will the function not exclude?
- How to extract the largest number ignoring error values
- Get Excel *.xlsx file
Extract the largest number ignoring error values using arrays (Link)
1. Introduction
It is designed for columns of data or vertical ranges.
The formula above sums all values in cell range B3:B7 excluding errors.
Formula in cell D3:
2. Syntax
Reference form
AGGREGATE(function_num, options, ref1, [ref2], …)
Array form
AGGREGATE(function_num, options, array, [k])
The animated image above demonstrates how the AGGREGATE function guides you while selecting the first argument function_num.
function_num | Required. A number 1 to 19 that lets you choose between different functions. See the list below. |
Function | |
1 | AVERAGE |
2 | COUNT |
3 | COUNTA |
4 | MAX |
5 | MIN. |
6 | PRODUCT |
7 | STDEVS.S |
8 | STDEVS.P |
9 | SUM |
10 | VAR.S |
11 | VAR.P |
12 | MEDIAN |
13 | MODE.SNGL |
14 | LARGE |
15 | SMALL |
16 | PERCENTILE.INC |
17 | QUARTILE.INC |
18 | PERCENTILE.EXC |
19 | QUARTILE.EXC |
The second argument is the options argument, the image above shows what choices you have.
Options | Required. A numerical value that allows you to choose which values to exclude in the calculation. |
0 or omitted. | Ignore nested SUBTOTAL and AGGREGATE functions |
1 | Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions |
2 | Ignore error values, nested SUBTOTAL and AGGREGATE functions |
3 | Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions |
4 | Ignore nothing |
5 | Ignore hidden rows |
6 | Ignore error values |
7 | Ignore hidden rows and error values |
The AGGREGATE function has two different forms: array and reference. The array form allows you to use the following functions:
- LARGE(array,k)
- SMALL(array,k)
- PERCENTILE.INC(array,k)
- QUARTILE.INC(array,quart)
- PERCENTILE.EXC(array,k)
- QUARTILE.EXC(array,quart)
The reference form has these arguments:
AGGREGATE(function_num, options, ref1, [ref2], …)
Ref1 | Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value. |
Ref2,... | Optional. Numeric arguments 2 to 253 for which you want the aggregate value. |
How do you choose the AGGREGATE form?
It depends on what function_num number you pick.
3. Example 1 - calculate the average
Calculate the arithmetic mean from these values [5, #DIV/0, 6, 4, #VALUE!]?
Here are the arguments:
Array form - AGGREGATE(function_num, options, array, [k])
- function_num - 1 (AVERAGE function)
- options - 6 (Ignore error values)
- array - B3:B7 (A cell reference to the values you want to calculate an average for.)
- [k] - optional (The AVERAGE function doesn't use the k argument, contrary to LARGE or SMALL functions.
The AVERAGE function calculates an average, however, it doesn't ignore error values or hidden rows. This is where the AGGREGATE function is really useful.
Formula in cell D3:
5 + 6 + 4 equals 15. 15/3 equals 5. This value matches the value in cell D3.
4. Example 2 - COUNT
Count the numerical values from this data set: [5, #DIV/0, 6, 4, #VALUE!]?
Here are the arguments:
Array form - AGGREGATE(function_num, options, array, [k])
- function_num - 2 (COUNT function)
- options - 6 (Ignore error values)
- array - B3:B7
- [k] - optional
Formula in cell D3:
Here is how the AGGREGATE evaluates the given arguments:
AGGREGATE(2, 6, B3:B7) becomes
AGGREGATE(2, 6, {5; #DIV/0!; 6; 4; #VALUE!}) and returns 3. There are three numbers in cell range B3:B7.
5. Example 3 - COUNTA
Count the non-empty values ignoring error values, from this data set: [5, #DIV/0, 6, 4, #VALUE!]?
Here are the arguments:
Array form - AGGREGATE(function_num, options, array, [k])
- function_num - 3 (COUNTA function)
- options - 6 (Ignore error values)
- array - B3:B7
- [k] - optional
Formula in cell D3:
Here is how the AGGREGATE evaluates the given arguments:
AGGREGATE(3, 6, B3:B7) becomes
AGGREGATE(3, 6, {5; #DIV/0!; ""; ""; #VALUE!}) and returns 1. There is only one non-empty value if you ignore the error values.
6. Example 4 - MAX
Calculate the maximum (largest) number ignoring error values, from this data set: [5, #DIV/0, 6, 4, #VALUE!]?
Here are the arguments:
Array form - AGGREGATE(function_num, options, array, [k])
- function_num - 4 (MAX function)
- options - 6 (Ignore error values)
- array - B3:B7
- [k] - optional
Formula in cell D3:
Here is how the AGGREGATE evaluates the given arguments:
AGGREGATE(3, 6, B3:B7) becomes
AGGREGATE(4, 6, {5; #DIV/0!; 6; 4; #VALUE!}) and returns 6. Six is the largest number in cell range B3:B7.
7. Example 5 - MIN
Calculate the minimum (smallest) number ignoring error values, from this data set: [5, #DIV/0, 6, 4, #VALUE!]?
Here are the arguments:
Array form - AGGREGATE(function_num, options, array, [k])
- function_num - 5 (MIN function)
- Array form - AGGREGATE(function_num, options, array, [k])
- options - 6 (Ignore error values)
- array - B3:B7
- [k] - optional
Formula in cell D3:
Here is how the AGGREGATE evaluates the given arguments:
AGGREGATE(5, 6, B3:B7) becomes
AGGREGATE(5, 6, {5; #DIV/0!; 6; 4; #VALUE!}) and returns 4. Four is the smallest number in cell range B3:B7.
8. Example 6 - PRODUCT
Calculate the PRODUCT (multiplication) ignoring error values, from this data set: [5, #DIV/0, 6, 4, #VALUE!]?
Here are the arguments:
Array form - AGGREGATE(function_num, options, array, [k])
- function_num - 6 (PRODUCT function)
- options - 6 (Ignore error values)
- array - B3:B7
- [k] - optional
Formula in cell D3:
Here is how the AGGREGATE evaluates the given arguments:
AGGREGATE(6, 6, B3:B7) becomes
AGGREGATE(6, 6, {5; #DIV/0!; 6; 4; #VALUE!}) and returns 120. 5 * 6 * 4 equals 120.
9. Example 7 - SUM
Calculate the sum (total) ignoring error values, from this data set: [5, #DIV/0, 6, 4, #VALUE!]?
Here are the arguments:
Array form - AGGREGATE(function_num, options, array, [k])
- function_num - 9 (SUM function)
- options - 6 (Ignore error values)
- array - B3:B7
- [k] - optional
Formula in cell D3:
Here is how the AGGREGATE evaluates the given arguments:
AGGREGATE(9, 6, B3:B7) becomes
AGGREGATE(9, 6, {5; #DIV/0!; 6; 4; #VALUE!}) and returns 15. 5 + 6 + 4 equals 15.
4. What will the AGGREGATE function not exclude?
The function will not exclude
- hidden rows
- nested subtotals
- nested aggregates if the array argument includes a calculation, for example:
When you type the first argument in the AGGREGATE function Excel returns a list of functions that are valid.
5. How to extract the largest number ignoring error values
The image above demonstrates how the AGGREGATE function is able to return the largest number and at the same time ignore error values.
Formula in cell D3:
This works fine as long as you are using cell references, however, it won't work if you try to use other functions in the third argument or use array values.
Useful links
AGGREGATE function - Microsoft
How to use the AGGREGATE Function (WS)
'AGGREGATE' function examples
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
Functions in 'Math and trigonometry' category
The AGGREGATE function function is one of 61 functions in the 'Math and trigonometry' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form