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 in 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 "function_num" > 19, then #VALUE! is returned.
* 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 arithmetic mean 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 values that are not empty cells.
* You can use the MAX function to return the largest value in a list, table or cell range.
* You can use the MIN function to return the smallest value in a list, table or cell range.
* You can use the PRODUCT function to return the product of all the numbers in a list, table or cell range.
* 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:B8) = 2810
2=AGGREGATE(2, 4, B1:B8) = 510
3=AGGREGATE(3, 4, B1:B8) = 730
4=AGGREGATE(4, 4, B1:B8) = 5040
5=AGGREGATE(5, 4, B1:B8) = 1050
6=AGGREGATE(6, 4, B1:B8) = 60000000
7=AGGREGATE(9, 4, B1:B8) = 140some text
8=AGGREGATE(12, 4, B1:B8) = 30 
9=AGGREGATE(13, 4, B1:B8) = 10 
10=AGGREGATE(9, 4, B1, B2, B3, B4, B5, B6) = 140 
11=AGGREGATE(14, 4, B1:B8, 1) = 50 
12=AGGREGATE(15, 4, B1:B8, 1) = 10 
13=AGGREGATE(111, 4, B1, B2, B3, B4, B5, B6) = #VALUE! 

1 - What is the "average (1)" of the values in range "B1:B8".
2 - What is the "count (2)" of the values in range "B1:B8".
3 - What is the "counta (3)" of the values in range "B1:B8".
4 - What is the "max (4)" of the values in range "B1:B8".
5 - What is the "min (5)" of the values in range "B1:B8".
6 - What is the "product (6)" of the values in range "B1:B8".
7 - What is the "sum (9)" of the values in range "B1:B8".
8 - What is the "median (12)" of the values in range "B1:B8".
9 - What is the "mode.sngl (13)" of the values in the range "B1:B8".
10 - What is the "sum (9)" of the values in the cells "B1", "B2", "B3", "B4", "B5" and "B6".
11 - What is the "large (14)" of the values in range "B1:B8". Notice the additional argument.
12 - What is the "small (15)" of the values in range "B1:B8". Notice the additional argument.
13 - The "function_num" is not valid.

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