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

* For an illustrated example refer to the page under Summing Functions
* Rows are always excluded when there are Nested Subtotals
* This function excludes empty cells.
* 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.
* This function was added in Excel 2010 to replace the SUBTOTAL function.
* For the Microsoft documentation refer to support.microsoft.com

	A	B
1	=AGGREGATE(1, 4, B1:B8) = 8	5
2	=AGGREGATE(2, 4, B1:B8) = 4	10
3	=AGGREGATE(3, 4, B1:B8) = 7	15
4	=AGGREGATE(4, 4, B1:B8) = 15
5	=AGGREGATE(5, 4, B1:B8) = 0	0
6	=AGGREGATE(6, 4, B1:B8) = 0	text
7	=AGGREGATE(9, 4, B1:B8) = 30	True
8	=AGGREGATE(12, 4, B1:B8) = 8	False
9	=AGGREGATE(13, 4, B1:B8) = #N/A	=DATE(2024, 1, 1) = 01 Jan 2024
10	=AGGREGATE(9, 4, B1, B2, B3, B4) = 30	=10/0 = #DIV/0!
11	=AGGREGATE(14, 4, B1:B8, 1) = 15
12	=AGGREGATE(15, 4, B1:B8, 1) = 0
13	=AGGREGATE(111, 4, B1) = #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.