**What Is AGGREGATE Excel Function?**

The

AGGREGATE Excel functionreturns the result of the specified aggregate operation performed on columns of data or the vertical range. Users can apply Excel functions such asSUM,AVERAGE,MAX,andCOUNT,using theAGGREGATE()function. It also provides the choice to ignore error values and hidden rows.

The **AGGREGATE function in Excel** is an inbuilt function which means we can enter the formula directly in the worksheet or insert it from the “**Math & Trig**” option from the “**Function Library**”.

For example, the first table below shows a student’s scores in eight subjects.

To update the results of the required calculations listed in the second table, enter the **AGGREGATE()** formulas **=AGGREGATE(9,4,B2:B9), =AGGREGATE(1,4,B2:B9), =AGGREGATE(4,4,B2:B9), **and** =AGGREGATE(14,4,B2:B9,2**) in the cells** E2, E3, E4, **and** E5, **respectively**.**

The output is shown above. The **AGGREGATE Excel function **in each target cell differs according to the specified aggregate operation, such as **SUM** and **AVERAGE** in cells **D2** and **D3**, respectively.

##### Table of contents

###### Key Takeaways

- The
**AGGREGATE Excel function**helps apply aggregate operations, such as**SUM**and**AVERAGE,**on columns of data or a vertical range. However, we cannot use the function for the row values or a horizontal cell range. - The functions give a choice to skip hidden rows and error values while executing the specific aggregate operation, thus overcoming the limitations of conditional formatting.
- The
**AGGREGATE()**has two syntax formats,**array**and**reference**. - Functions such as
**LARGE**,**SMALL**,**PERCENTILE**, and**QUARTILE**require the additional argument**k**.

**AGGREGATE() Excel Formula**

The **AGGREGATE Excel formula** has two syntaxes, namely,

**Array Format.****Reference Format**

**#Syntax 1 – The Array Format **syntax of the **AGGREGATE Excel formula** is:

**The Array Format **arguments of the **AGGREGATE Excel formula** are:

: A number that refers to the aggregate operation we want to perform. It can be from 1 to 19. It is a mandatory argument.*function_num*

function_num | Function |
---|---|

1 | AVERAGE |

2 | COUNT |

3 | COUNTA |

4 | MAX |

5 | MIN |

6 | PRODUCT |

7 | STDEV.S |

8 | STDEV.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 |

: A number that indicates which values we require the*options***AGGREGATE Excel function**to skip in the dataset while executing the calculations. It can be from 0 to 7. It is a mandatory argument.

Options | Values- Ignored |
---|---|

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 reference to an array of values on which we require to apply the*array***AGGREGATE()**. It is a mandatory argument.: An integer representing the required data’s position in the array. Typically, functions such as*k***LARGE**,**SMALL**,**PERCENTILE**, and**QUARTILE**require this additional argument. It is an optional argument.

Function | k- Interpretation |
---|---|

LARGE | Return the k^{th} largest value |

SMALL | Return the k^{th} smallest value |

PERCENTILE.INC | Return the k^{th} percentile value |

PERCENTILE.EXC | Return the k^{th} percentile value |

QUARTILE.INC | Return the k^{th} quartile value |

QUARTILE.EXC | Return the k^{th} quartile value |

**#Syntax 2 – The Reference Format **syntax of the **AGGREGATE Excel formula** is:

**The Reference Format **arguments of the **AGGREGATE Excel formula** are:

and*function_num***options****Array Format**.

*ref1,**…*: The numeric values on which we require to apply the aggregation function. And the function can accept 2 to 253 ref arguments.

The argument’s *function_num,** options, ref1, *and

*are mandatory in this syntax format. And the following numeric values after*

**ref2****are optional.**

*ref2*Below are a few critical facets of the **AGGREGATE Excel function** we should consider while using the function:

- If the aggregate operation we want to perform requires the
**ref2**value and we do not supply it to the**AGGREGATE()**, the function will return the**#VALUE!** - Suppose we provide 3-D references as
**ref**arguments to the**AGGREGATE()**, then the function will return the**#VALUE!** - The
**AGGREGATE()**function does not work for horizontal ranges of values.

**How To Use AGGREGATE Excel Function?**

We can use the **AGGREGATE Excel function** in 2 methods, namely,

**Access from the Excel Ribbon.****Enter in the worksheet manually.**

**#Method 1 – Access from the Excel Ribbon**

First, choose the target output cell → select the “**Formulas**” tab → go to the “**Function Library**” group → click the “**Math & Trig**” drop-down → select the “**AGGREGATE**” function, as shown below.

The “**Select Arguments**” window pops up.

- If we choose the
**Array Format**and click “**OK**”, the “**Function Arguments**” window opens with the “**Array Format**” argument fields, as shown below. Enter the arguments accordingly and click “**OK**”.

- If we choose the
**Reference Format**and click “**OK**”, the “**Function Arguments**” window opens with the “**Reference Format**” argument fields, as shown below. Enter the arguments accordingly and click “**OK**”.

**#Method 2 – Enter in the worksheet manually**

- First, ensure our source data is complete and in vertical ranges.
- Next, select the target cell for the output.
- Type the formula
**=AGGREGATE(.**[Alternatively, type**=A**and double-click the**AGGREGATE Function**from the list of suggestions from Excel]. - Press the “
**Enter**” key. We will get two syntaxes to select. - Select the “
**Array Format or Reference Format**” accordingly. - Finally, press the “
**Enter**” key.

**#Basic Example**

We will find the number of cells containing the employee’s information and the number of cells containing numbers** using** **AGGREGATE Excel function.**

The below table contains an employee’s information.

The steps to get employee details **using** **AGGREGATE Excel function **are:

**Select cell B13, enter the formula =AGGREGATE(, choose 3 [COUNTA] in the drop-down list that appears, as shown below. The formula so far is =AGGREGATE(3****In continuation with the formula, enter a ‘,’ after which we will see a drop-down list to select the options argument. In this case, we shall double-click on option 4 (Ignore nothing). The formula now is =AGGREGATE(3,4****In continuation with the formula, enter a ‘,’, choose the array range i.e., B2:B11, and close the brackets. The complete formula is =AGGREGATE(3,4,B2:B11).****Finally, press the “Enter” key. The output is 10, as shown below.**

[Alternatively, select cell**B13**and click**Formulas**→**Math & Trig**→**AGGREGATE**to open the**Function Arguments**window.

The below dialog box will pop up. Then, choose the**Array**format of the**AGGREGATE()**syntax**.**Then**,**press**OK**to open the**Function Arguments**window.

Enter the**AGGREGATE()**arguments in the**Function Arguments**window as cell values or cell references.

And once we click**OK**, the**COUNTA()**output will appear in the target cell**B13**.]**Next, to determine the number of cells containing numbers in the cell range B2:B11, select cell B14, enter the formula***=AGGREGATE(2,4,B2:B11)*, and press the “Enter” key. [Note: Double-click on 2 to choose the COUNT().]

The output is**4**, as shown above, as cells**B4, B5, B7,**and**B8**contain numbers.

The output is **4**, as shown above, as cells **B4, B5, B7, **and** B8** contain numbers.

**Examples**

We will understand some advanced scenarios using the **AGGREGATE Excel function examples**.

**#Example 1**

We will use the reference format of **AGGREGATE Excel function **syntax to determine the median annual salary from the listed salaries.

The following table shows the salaries of fifteen employees.

The procedure to apply the **AGGREGATE Excel function** is:

First, select cell **B18**, enter the formula, using the reference format of syntax, **=AGGREGATE(12,4,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13,B14,B15,B16), **and press the “**Enter**” key.

The output is **75800**, as shown above.

**[Special Note: **Refer to steps 1, and 2 explained in the previous section to update the **function_num** and **options** arguments in the **AGGREGATE()**.

The function will align the 15 values in numeric order. And as the dataset has an odd number of data points, the function returns the middle number as the median annual salary.

Alternatively, we can use the array format of the **AGGREGATE() **syntax to get the same result:

*=AGGREGATE(12,4,B2:B16)*

We might find applying the **MEDIAN() **more convenient. But the **AGGREGATE()** gives the desired results even when our dataset might have errors, hidden rows, or nested **SUBTOTAL()** and **AGGREGATE()**.]

**#Example 2**

We will use the **AGGREGATE Excel function **when the dataset contains error values and hidden rows.

The following table contains a list of prime numbers or factors.

To determine the product of the prime numbers, if we apply the **PRODUCT() **in the target cell **B12**, the output will be an error value, as cell **B5** contains an error value.

The procedure to apply the **AGGREGATE Excel function **to get the required result despite an error value is:

First, select cell **B12**, enter the formula ** =AGGREGATE(6,6,B2:B11), **and press the “

**Enter**” key.

The **function_num** argument is **6 **(**PRODUCT**), and **the options** argument is **6** (**Ignore error values**). Thus the **AGGREGATE() **ignores the error value in cell **B5** and returns the product of the remaining data points.

Another scenario is if we hide row **7** and the error value in cell **B5**. And we do not want to include the hidden cell **B7** value in the product.

So, now the formula will be: *=AGGREGATE(6,7,B2:B11)*

Here the argument **options** value is **7** (**Ignore hidden rows and error values**). Thus, the **AGGREGATE() **does not consider the hidden value (cell **B7**) and error value (cell **B5**) while calculating the multiplication product of the prime factors.

Thus, we can use the argument **options** to decide the values we want to ignore while applying the specific aggregate operation.

**#Example 3**

The **AGGREGATE Excel function **can help us process arrays for specific operations without using **Ctrl** + **Shift** + **Enter**. For Example, we will find the maximum quantity ordered for a particular fruit.

The following table shows a list of fruits and their order details.

If we use the **LARGE() function**, it will involve array ranges. To execute it as an array formula, press **Ctrl** + **Shift** + **Enter **instead of the **Enter **key. Moreover, one of the ordered quantities for **Banana **is an error value (cell **C19**), so the **LARGE() **will return an error, as shown below.

Instead, the steps to apply the **AGGREGATE Excel function **to get the required result are:

__Step 1__**: **Select cell **G2**, enter the formula ** =AGGREGATE(14,6,C2:C21*(A2:A21=F2),1)**, and press the “

**Enter**” key. The output is

**650**, as shown below.

The above function has **function_num** **14**, which denotes the **LARGE()**. And the **options** argument is **6**, which indicates **Ignore error values**.

On the other hand, the term **(A2:A21=F2) **returns **TRUE **for cells in column A containing the item **Banana** and **FALSE **for other cells in column A. The term **C2:C21** lists the quantities in column C. And the term, **C2:C21*(A2:A21=F2)**, lists the quantities cell-wise, showing the values corresponding to **Banana **and **0 **for other items. And as we require the largest quantity, the **k** value is **1**.

Finally, the **AGGREGATE()** determines the largest number from the quantity list,** 650**, while ignoring the error value.

**Important Things To Note **

- The
**AGGREGATE()**argument,**function_num**, can take a value from 1 to 19. And the argument,**options**, can take a value from 0 to 7. Any values entered apart from these make the function return the**#VALUE!**error. - The function output will be the
**#VALUE!**error, if the aggregate operation we select requires the**ref2**value and we do not provide it. - We will also get a
**#VALUE!**Error if the**ref**arguments in the**AGGREGATE()**are 3-D references.

**Frequently Asked Questions**

**How to use the SUM AGGREGATE Excel function?**

We can use the **SUM AGGREGATE Excel function** to determine the total units ordered.

The following table shows our source data, a list of gadgets, and ordered units with few error values.

The steps to apply the **SUM AGGREGATE** function are:** Step 1: **Select cell

**B13**and enter as indicated. First, enter

**=AGGREGATE(**, double-click number

**9**(

**SUM**).

**Then enter a ‘**

__Step 2__:**,**’ and double-click

**6**(

**Ignore error values**) to enter the

**options**argument.

**Then enter a ‘**

__Step 3__:**,**’ and select the array range to add the data points. Close the brackets.

The complete formula is

**=AGGREGATE(9,6,B2:B11).**

**Press the “**

__Step 4__:**Enter**” key.

The output is 1890, as shown above i.e., the sum value, ignoring the error values.

**Which values does the AGGREGATE Excel function ignore?**

The **AGGREGATE Excel function** ignores:

1. Hidden rows.

2. Error-values.

3. Nested **SUBTOTAL()** and **AGGREGATE().**

**When to use the AGGREGATE Excel function?**

We can use the **AGGREGATE Excel function** to execute a specific aggregate operation, such as **SUM** or **MEDIAN**, for a data column or a vertical range.

**Download Template**

This article must help understand the **AGGREGATE Excel function**, with its formula and examples. We can download the template here to use it instantly.

### Recommended Articles

This has been a guide to AGGREGATE Excel function. Here we learn how to use Aggregate formula along with step by step examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply