# AGGREGATE

## Content

The AGGREGATE function returns an aggregate in a list or database. Users can apply different aggregate functions like COUNT, AVERAGE, SUM, MAX, MIN, etc. to a list while ignoring "error values" and "hidden rows" in the spreadsheets.
The AGGREGATE function is useful, especially when:

* Users want to apply filters to the data and it is necessary to ignore the cells that were hidden with the filter while adding all the values together.
* Users want to stop propagating errors throughout the worksheet.
* Users want to manipulate data such as finding the average of the results of multiple formulas while ignoring errors like "returning invalid numbers" or other types of errors etc.

## Syntax

While working with the AGGREGATE function, users can use the following syntax:

* `AGGREGATE(function_num, options, ref1, [ref2], …)`
    This syntax is applicable only when the *function\_num* argument contains values ranging from 1 to 13. For details, refer to the table shown below.
* `AGGREGATE(function_num, options, array, [k])`
    This syntax is applicable only when the *function\_num* argument contains values ranging from 14 to 19. For details, refer to the table shown below.

## Arguments

This function has the following arguments:

| Argument | Description |
| -------- | ----------- |
| *function\_num* | [Required] Refers to the number (within the range 1-19) to specify the function that is being used. |
| *options* | [Required] Refers to a particular number that specifies the options to ignore values in the function. |
| ref1 | [Required] Specifies the reference to a range of cells. |
| ref2 | [Optional] Specifies the reference to a range of cells. |
| array | [Required] Only when *function\_num* argument above contains values ranging from 14 to 19, Specifies an array, an array formula, or a reference to a range of cells. |
| k | [Required] This argument is required to specify the functions when the *function\_num* values are ranging from 14-19. |

The following things must be kept in mind while using the AGGREGATE function:

* If the arguments array, k, or ref1 is not provided, the AGGREGATE function will return the #VALUE! error.
* If argument k is provided as an array, then the first value is used. If argument k is provided as a cell reference with one column, the value in last row is used. ( For e.g. {1,2,3,4}=>1,   A1:A23  => A23, A1:B23 => #VALUE!, A1:C1 => #VALUE!)
* References don't support 3-D references. The function will returns #VALUE! if 3-D references are used (For e.g: "Sheet1:Sheet3!B3" will return #VALUE! error.).
* Ignore hidden doesn't support hidden columns.
* The rows hidden by filter/ hide/group operations will support the ignore feature.
* If the array includes a calculation, AGGREGATE function will not ignore hidden rows. (eg: "=AGGREGATE(14,3,A1:A6\*123,1)")

## Examples

![](https://cdn.mescius.io/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/spreadjsv13images/aggregate.png)