Formula Reference
Contacting Us
Getting Technical Support
Formula Overview
Formula Functions
- Functions A to C
  - ABS
  - ACCRINT
  - ACCRINTM
  - ACOS
  - ACOSH
  - ACOT
  - ACOTH
  - ADDRESS
  - AGGREGATE
  - AMORDEGRC
  - AMORLINC
  - ANCHORARRAY
  - AND
  - ARABIC
  - AREAS
  - AREASPARKLINE
  - ARRAYTOTEXT
  - ASC
  - ASIN
  - ASINH
  - ATAN
  - ATAN2
  - ATANH
  - AVEDEV
  - AVERAGE
  - AVERAGEA
  - AVERAGEIF
  - AVERAGEIFS
  - BAHTTEXT
  - BASE
  - BESSELI
  - BESSELJ
  - BESSELK
  - BESSELY
  - BETA.DIST
  - BETA.INV
  - BETADIST
  - BETAINV
  - BIN2DEC
  - BIN2HEX
  - BIN2OCT
  - BINOM.DIST
  - BINOM.DIST.RANGE
  - BINOM.INV
  - BINOMDIST
  - BITAND
  - BITLSHIFT
  - BITOR
  - BITRSHIFT
  - BITXOR
  - BOXPLOTSPARKLINE
  - BULLETSPARKLINE
  - BYCOL
  - BYROW
  - CASCADESPARKLINE
  - CEILING
  - CEILING.MATH
  - CEILING.PRECISE
  - CELL
  - CHAR
  - CHIDIST
  - CHIINV
  - CHISQ.DIST
  - CHISQ.DIST.RT
  - CHISQ.INV
  - CHISQ.INV.RT
  - CHISQ.TEST
  - CHITEST
  - CHOOSE
  - CHOOSECOLS
  - CHOOSEROWS
  - CLEAN
  - CODE
  - COLUMN
  - COLUMNS
  - COLUMNSPARKLINE
  - COMBIN
  - COMBINA
  - COMPLEX
  - CONCAT
  - CONCATENATE
  - CONFIDENCE
  - CONFIDENCE.NORM
  - CONFIDENCE.T
  - CONVERT
  - CORREL
  - COS
  - COSH
  - COT
  - COTH
  - COUNT
  - COUNTA
  - COUNTBLANK
  - COUNTIF
  - COUNTIFS
  - COUPDAYBS
  - COUPDAYS
  - COUPDAYSNC
  - COUPNCD
  - COUPNUM
  - COUPPCD
  - COVAR
  - COVARIANCE.P
  - COVARIANCE.S
  - CRITBINOM
  - CSC
  - CSCH
  - CUMIPMT
  - CUMPRINC
- Functions D to G
- Functions H to L
- Functions M to Q
- Functions R to S
- Functions T to Z

AGGREGATE

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	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
STDEV.S	7
STDEV.P	8
SUM	9
VAR.S	10
VAR.P	11
MEDIAN	12
MODE.SNGL	13
LARGE	14
SMALL	15
PERCENTILE.INC	16
QUARTILE.INC	17
PERCENTILE.EXC	18
QUARTILE.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.