AGGREGATE function in Excel is a powerful Math and trigonometry function comprising 19 various functions like AVERAGE, SUM, COUNT, MAX etc. and eight options (0 to 7).

8 options are used as 2nd argument to ignore behaviour like hidden rows, error values, nested SUBTOTAL and AGGREGATE etc. by selecting the number specified to respective behaviour during the calculation process.

When a range of cells in a column has numeric values, hidden rows, and errors, and we try to sum the said range using the Excel **SUM** function then we get an error value as a result, but we can get an error-free sum using the AGGREGATE function.

AGGREGATE function is an upgraded version of the SUBTOTAL function. It is a user-friendly function as the Array Syntax of this function can be used without pressing Ctrl+Shift+Enter.

**Table of Contents**

## Syntax of AGGREGATE Function

AGGREGATE function has two Syntaxes:

### 1. **Reference Syntax**:

AGGREGATE(function_num, options, ref1, [ref2], …)

### 2. **Array Syntax**:

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

**Arguments of **Reference Syntax

**Function_num**

*function_num* is the 1st required argument containing 19 functions (as mentioned in the below table). You have to enter the number of the function you want to use.

**Options**

*options*** **is the 2^{nd} required argument having numeric values (0 to 7). It is used to ignore specific values like hidden rows, error values, nested SUBTOTAL and AGGREGATE functions or to ignore the combination of these specific values.

Numbers and their respective values are mentioned in the below image. Enter the number of the value you want to ignore in the calculation. If nothing is entered in this argument then it is considered as omitted and set to zero (0).

**Ref1**

*ref1* is the 3^{rd} required argument of AGGREGATE function, but It is the initial numeric argument for the function or functions you want to apply for the calculation of the aggregate value. It can be either a reference to a range of cells or an array.

**[Ref2]**,…

*[ref2]* is an optional argument that is required only for the below-mentioned last six functions (14 to 19) of the AGGREGATE function. Under this optional argument, the minimum reference of numeric argument is [ref2] and maximum [ref253]:

## Arguments of Array Syntax

**Function_num**

same as of Reference Syntax.

**Options**

Same as of Reference Syntax

**Array**

Required argument, array means a reference to a range of cells, an array formula, or **ref1** for which value is to be calculated. Following 6 functions require an **array**.

Function No. | Function |
---|---|

14 | LARGE(array,k) |

15 | PERCENTILE.INC(array,k) |

16 | SMALL(array,k) |

17 | QUARTILE.INC(array,quart) |

18 | PERCENTILE.EXC(array,k) |

19 | QUARTILE.EXC(array,quart) |

**Table-1**

**K**

K is an optional argument of ‘Array Syntax’ and used as a numeric value in 5 functions mentioned in above Table-1.

## How to use AGGREGATE Function in Excel?

To use the Excel AGGREGATE function, type ‘**=**’ (** is equal to**) followed by

**AGGREGATE**in a cell and press the Tab key to see the Syntax of the AGGREGATE function. Now, enter the required arguments of the chosen Syntax as explained above:

**Examples:**

**Example #1:** Sum using AGGREGATE and SUM formula when there is an error value in a range of cells

**Example #2: **Sum using AGGREGATE and SUM formula when there is an error value in a range of cells and a row is hidden

## Limitations of Excel AGGREGATE Function

- AGGREGATE function works on columns of data as it has been designed for vertical ranges, and not for horizontal ranges or rows of data.

- Excel AGGREGATE formula returns a #VALUE! error, if required 2nd ref argument is not provided.

- AGGREGATE also returns #VALUE! error, if any of the 3D-references is used.

- If a column of data is hidden then it doesn’t affect the result of the Excel AGGREGATE function, but when a row of data is hidden then it will exclude the value of hidden row from the result of AGGREGATE Excel function.

Very nice explanation.

Good job. If you explain it with your voice then it will be more effective