# Excel AGGREGATE function

## Summary

The Excel AGGREGATE function is a versatile tool for performing a variety of calculations like SUM, AVERAGE, COUNT, MAX, and more, with options to ignore errors, hidden rows, or nested subtotals. It is especially useful in complex datasets where certain elements need to be excluded from calculations.
##### Syntax
```				```
=AGGREGATE(function_num, options, ref1, [ref2], ...)
```
```
• function_num: Specifies the function to use (e.g., 1 for AVERAGE, 9 for SUM)
• options: Specifies which values to ignore in the operation
• ref1, [ref2], …: The range or ranges on which to perform the calculation
##### Return value

The result of the chosen aggregate function, depending on the specified options.

## How to use

Select the desired function using function_num, set options to control how to handle errors, hidden rows, or nested subtotals, and then specify the range(s) for the calculation.

function_num values:

• `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

options values:

• `0`: Ignore nested SUBTOTAL and AGGREGATE functions
• `1`: Ignore hidden rows, nested SUBTOTAL and AGGREGATE
• `2`: Ignore error values, nested SUBTOTAL and AGGREGATE
• `3`: Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE
• `4`: Ignore nothing
• `5`: Ignore hidden rows
• `6`: Ignore error values
• `7`: Ignore hidden rows and error values

## Examples

##### Simple AGGREGATE
Summing Values while Ignoring Errors: To sum a range while ignoring error values:
```				```
=AGGREGATE(9, 6, A2:A10)
```
```
This formula sums the values in A2:A10, excluding any errors found within the range.
##### AGGREGATE for Maximum Value
Finding the Maximum Value Excluding Hidden Rows: To determine the maximum value in a range, ignoring hidden rows:
```				```
=AGGREGATE(4, 5, A2:A10)
```
```
This computes the maximum value in A2:A10 while ignoring any values in hidden rows.
##### AGGREGATE with COUNTA Function
Counting Non-Empty Cells, Ignoring Errors: To count non-empty cells in a range, excluding error values:
```				```
=AGGREGATE(3, 6, A2:A10)
```
```
Counts the number of non-empty cells in A2:A10, ignoring cells with errors.
##### AGGREGATE with AVERAGE Function
Calculating Average, Excluding Zeros and Errors: To calculate the average of a range, excluding zeros and errors:
```				```
=AGGREGATE(1, 6, A2:A10)
```
```
This formula averages the values in A2:A10, ignoring both zero values and errors.
##### AGGREGATE in Financial Calculations
Summing Financial Data, Ignoring Subtotals and Hidden Rows: To sum financial data while ignoring subtotals and hidden rows:
```				```
=AGGREGATE(9, 7, A2:A10)
```
```
This sums the values in A2:A10, but excludes any hidden rows and subtotals.