AGGREGATE

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

Returns the choice of 19 different operations for numerical values which can ignore hidden rows and errors.

function_num	The 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
options	The 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
ref1	The 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 numerical values.
* You can use the COUNT function to return the number of numerical values.
* 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 numerical value.
* You can use the MIN function to return the smallest numerical value.
* You can use the PRODUCT function to return the product of all the numerical values.
* You can use the SUM function to return the total of the numerical values.
* For the Microsoft documentation refer to support.microsoft.com

	A	B
1	=AGGREGATE(1, 4, B1:B8) = 28	10
2	=AGGREGATE(2, 4, B1:B8) = 5	10
3	=AGGREGATE(3, 4, B1:B8) = 7	30
4	=AGGREGATE(4, 4, B1:B8) = 50	40
5	=AGGREGATE(5, 4, B1:B8) = 10	50
6	=AGGREGATE(6, 4, B1:B8) = 6000000	0
7	=AGGREGATE(9, 4, B1:B8) = 140	some 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.