# AGGREGATE

Spread for WinForms provides advanced software components that support Excel import/export, full cell customization, an extensive calculation engine with over 450 functions and more, all with zero dependencies on Excel.

## Content



This function calculates an aggregate value in a list or database.

## Syntax

AGGREGATE(*functionnum*, *options*, *reference1*, *reference\_\_2*, …)
or
AGGREGATE(*functionnum*, *options*, *array*, *k*)

## Arguments

This function has the following arguments:

| **Argument** | **Description** |
| -------- | ----------- |
| *functionnum* | Refers to the number code that specifies the function to use (see table below). |
| *options* | Refers to the numerical value that determines which values must be ignored in the evaluation range for the function (see table below) |
| *reference1* | Refers to the argument for which you want the aggregate value. |
| *reference2* | [Optional] Refers to the additional arguments for which you want the aggregate value. |
| *array* | Refers to an array, array formula, or a reference to a range of cells for which you want the aggregate value. |
| *k* | Refers to the additional arguments for which you want the aggregate value. |

The *functionnum* argument is the number that represents the built-in function to use, as given in this table.

| **Built-In Function** | **Function Code** |
| ----------------- | ------------- |
| [AVERAGE](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsAtoC/FunctionAVERAGE) | 1 |
| [COUNT](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsAtoC/FunctionCOUNT) | 2 |
| [COUNTA](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsAtoC/FunctionCOUNTA) | 3 |
| [MAX](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/FunctionMAX) | 4 |
| [MIN](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/FunctionMIN) | 5 |
| [PRODUCT](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/FunctionPRODUCT) | 6 |
| [STDEV.S](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsRtoS/FunctionSTDEV.S) | 7 |
| [STDEV.P](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsRtoS/FunctionSTDEV.P) | 8 |
| [SUM](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsRtoS/FunctionSUM) | 9 |
| [VAR.S](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsTtoZ/FunctionVAR.S) | 10 |
| [VAR.P](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsTtoZ/FunctionVAR.P) | 11 |
| [MEDIAN](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/FunctionMEDIAN) | 12 |
| [MODE.SNGL](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/functionMODE.SNGL) | 13 |
| [LARGE](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsFtoK/FunctionLARGE) | 14 |
| [SMALL](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsRtoS/FunctionSMALL) | 15 |
| [PERCENTILE.INC](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/functionPERCENTILE.INC) | 16 |
| [QUARTILE.INC](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/functionQUARTILE.INC) | 17 |
| [PERCENTILE.EXC](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/functionPERCENTILE.EXC) | 18 |
| [QUARTILE.EXC](/spreadnet/docs/latest/online-formula/formulas-functionsall/formulas-functsMtoQ/functionQUARTILE.EXC) | 19 |

The *options* argument has the following options.

| **Option** | **Description** |
| ------ | ----------- |
| 0 or omitted | Ignore nested SUBTOTAL and AGGREGATE functions |
| 1 | Ignore hidden rows and nested SUBTOTAL and AGGREGATE functions |
| 2 | Ignore error values and nested SUBTOTAL and AGGREGATE functions |
| 3 | Ignore hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions |
| 4 | Ignore nothing |
| 5 | Ignore hidden rows |
| 6 | Ignore error values |
| 7 | Ignore hidden rows and error values |

## Remarks

This function is designed for columns of data, or vertical cell range. This function has an option to ignore hidden rows and error values.
If a second *reference* argument is required but it is not provided by the user, a #VALUE! error is returned. Also, if one or more of the references are 3-D references, a #VALUE! error value is returned.
The *reference2* argument is optional; however, the following functions are required in this argument.

* LARGE
* SMALL
* PERCENTILE.INC
* QUARTILE.INC
* PERCENTILE.EXC
* QUARTILE.EXC

## Data Types

Accepts numeric values. Returns numeric data.

## Examples

`AGGREGATE(14,6,B1:B7,3)` gives the result 5, where B1:B7 is the cell range containing a numeric list.

## Version Available

This function is available in Spread for Windows Forms 11.0 or later.