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.

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

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.

Back to top

Useful links

AGGREGATE function - Microsoft
How to use the AGGREGATE Function (WS)

'AGGREGATE' function examples

Functions in 'Math and trigonometry' category

The AGGREGATE function function is one of 62 functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Leave a Comment