AGGREGATE(function_num, options, ref1 [,ref2])

Returns the aggregate of values in a list, table or cell range.


function_numThe type of function to use:
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
optionsA number indicating which values to ignore:
0 = 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
ref1The first reference you want to aggregate.
ref2(Optional) The second reference.

* Added in 2010.
* This function replaces the SUBTOTAL function.
* Maximum of 253 arguments.
* If "function_num" = LARGE(14), then a second "ref2" argument must be supplied.
* If "function_num" = SMALL(15), then a second "ref2" argument must be supplied.
* If "function_num" = PERCENTILE.INC(16), then a second "ref2" argument must be supplied.
* If "function_num" = QUARTILE.INC(17), then a second "ref2" argument must be supplied.
* If "function_num" = PERCENTILE.EXC(18), then a second "ref2" argument must be supplied.
* If "function_num" = QUARTILE.EXC(19), then a second "ref2" argument must be supplied.
* If a second "ref2" argument is required but not provided, then #VALUE! is returned.
* If any of the arguments are 3D references, then #VALUE! is returned.
* For an illustrated example refer to the page under Advanced Functions.
* For the Microsoft documentation refer to support.office.com.

 AB
1=AGGREGATE(1,4,B1:B6) = 3010
2=AGGREGATE(2,4,B1:B6) = 520
3=AGGREGATE(3,4,B1:B6) = 530
4=AGGREGATE(4,4,B1:B6) = 5040
5=AGGREGATE(5,4,B1:B6) = 1050
6=AGGREGATE(6,4,B1:B6) = 12000000 
7=AGGREGATE(7,4,B1:B6) = 16 
8=AGGREGATE(8,4,B1:B6) = 14 
9=AGGREGATE(9,4,B1:B6) = 150 
10=AGGREGATE(10,4,B1:B6) = 250 
11=AGGREGATE(11,4,B1:B6) = 200 
12=AGGREGATE(11,4,B1,B2,B3,B4,B5,B6) = 200 
13=AGGREGATE(111,4,B1,B2,B3,B4,B5,B6) = #VALUE! 
14=AGGREGATE(12,4,B1:B6) = 30 


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top