Menu
EXCEL AGGREGATE FUNCTION
Excel AGGREGATE Function Introduction
This is a new function in Excel 2010 that is quite flexible, since it combines several functions into a single function. Depending on the value of function num, the aggregate function can be utilised as SUM, COUNT, MIN, or AVERAGE.
=AGGREGATE(9,0,A1:A2) returns the SUM of the cells A1 and A2, whereas =AGGREGATE(1,0,A1:A2) returns the AVERAGE.
Description of Excel AGGREGATE Function
This function is available in two versions. The Array form and the Reference form are two different types of data structures.
Syntax:
AGGREGATE(function_num, options, ref1, [ref2], …)
- function num – a number between 1 and 13, as given in the function num Table below, to specify the function.
- options – a number that specifies which values in the function’s evaluation range should be ignored, as indicated in the table below.
- ref1 – the initial reference or numeric input for which the aggregate value should be calculated.
- references or numeric arguments 2 to 253 for which the aggregate value is desired [optional].
Step By Step Guide of AGGREGATE Function
Key Points
- If the array parameter includes a computation, such as =AGGREGATE(14,3,A1:A10*(A1:A10>0),1), the method will not disregard hidden rows, nested subtotals, or nested aggregates.
- AGGREGATE throws a #VALUE!error if a second ref argument is expected but not given.
- AGGREGATE returns the #VALUE! error value if one or more of the references are 3D references.
The AGGREGATE function is intended for vertical ranges or columns of data.
Function_num Table:
Function_num | Function_Syntax Form | |
1 | AVERAGE | Reference |
2 | COUNT | Reference |
3 | COUNTA | Reference |
4 | MAX | Reference |
5 | MIN | Reference |
6 | PRODUCT | Reference |
7 | STDEV.S | Reference |
8 | STDEV.P | Reference |
9 | SUM | Reference |
10 | VAR.S | Reference |
11 | VAR.P | Reference |
12 | MEDIAN | Reference |
13 | MODE.SNGL | Reference |
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
