AGGREGATE

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
optionsThe 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.

REMARKS
* This function was added n Excel 2010 to replace the SUBTOTAL function.
* For an illustrated example refer to the page under Advanced Functions
* Rows are always excluded when there are Nested Subtotals
* This function is useful when you need to get the count of rows that have been filtered using the AutoFilter.
* You can have a maximum of 253 arguments.
* If "function_num" = 14, 15, 16, 17, 18 or 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.
* You can use the AVERAGE function to return the averge of the numbers in a list, table or cell range.
* You can use the COUNT function to return the number of numerical values in a list, table or cell range.
* You can use the COUNTA function to return the number of numerical values in a list, table or cell range (including logical values and text).
* You can use the SUM function to return the total value of the numbers in a list, table or cell range.
* For the Microsoft documentation refer to support.microsoft.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 


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top