How to use 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.
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:
Table of Contents
- AGGREGATE function Syntax
- AGGREGATE function Arguments
- AGGREGATE function examples
- What will the AGGREGATE 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. AGGREGATE Function Syntax
Reference form
AGGREGATE(function_num, options, ref1, [ref2], …)
Array form
AGGREGATE(function_num, options, array, [k])
2. AGGREGATE Function Arguments
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. AGGREGATE function examples
3.1 AGGREGATE function - calculate the average
The AVERAGE function calculates an average, however, it doesn't ignore error values or hidden rows. This is where the AGGREGATE function is useful.
Formula in cell D3:
Arguments
function_num - 1Â Â (AVERAGE function)
Array form - AGGREGATE(function_num, options, array, [k])
options - 6Â Â Â Â Â Â Â Â Â (Ignore error values)
array - B3:B7
[k] - optional
5 + 6 + 4 equals 15. 15/3 equals 5.
3.2 AGGREGATE function - COUNT
Formula in cell D3:
Arguments
function_num - 2Â Â (COUNT function)
Array form - AGGREGATE(function_num, options, array, [k])
options - 6Â Â Â Â Â Â Â Â Â (Ignore error values)
array - B3:B7
[k] - optional
AGGREGATE(2, 6, B3:B7)
becomes
AGGREGATE(2, 6, {5; #DIV/0!; 6; 4; #VALUE!})
and returns 3. Therer are three numbers in cell range B3:B7
3.3 AGGREGATE function - counta
Formula in cell D3:
Arguments
function_num - 3Â Â (COUNTA function)
Array form - AGGREGATE(function_num, options, array, [k])
options - 6Â Â Â Â Â Â Â Â Â (Ignore error values)
array - B3:B7
[k] - optional
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.
3.4 AGGREGATE function - max
Formula in cell D3:
Arguments
function_num - 4Â Â (MAX function)
Array form - AGGREGATE(function_num, options, array, [k])
options - 6Â Â Â Â Â Â Â Â Â (Ignore error values)
array - B3:B7
[k] - optional
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.
3.5 AGGREGATE function - min
Formula in cell D3:
Arguments
function_num - 5Â Â (MIN function)
Array form - AGGREGATE(function_num, options, array, [k])
options - 6Â Â Â Â Â Â Â Â Â (Ignore error values)
array - B3:B7
[k] - optional
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.
3.6 AGGREGATE function - product
Formula in cell D3:
Arguments
function_num - 6Â Â (PRODUCT function)
Array form - AGGREGATE(function_num, options, array, [k])
options - 6Â Â Â Â Â Â Â Â Â (Ignore error values)
array - B3:B7
[k] - optional
AGGREGATE(6, 6, B3:B7)
becomes
AGGREGATE(6, 6, {5; #DIV/0!; 6; 4; #VALUE!})
and returns 120. 5 * 6 * 4 equals 120.
3.7 AGGREGATE function - sum
Formula in cell D3:
Arguments
function_num - 9Â Â (SUM function)
Array form - AGGREGATE(function_num, options, array, [k])
options - 6Â Â Â Â Â Â Â Â Â (Ignore error values)
array - B3:B7
[k] - optional
AGGREGATE(6, 6, B3:B7)
becomes
AGGREGATE(6, 6, {5; #DIV/0!; 6; 4; #VALUE!})
and returns 120. 5 * 6 * 4 equals 120.
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 […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A.  Cell […]
Functions in 'Math and trigonometry' category
The AGGREGATE function function is one of many 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