WorksheetFunction.Aggregate method (Excel)

Returns an aggregate in a list or database.

Syntax

expression.Aggregate (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

expression A variable that represents a WorksheetFunction object.

Parameters

Name	Required/Optional	Data type	Description
Arg1	Required	Double	Function_num - A number from 1 to 19 that specifies which function to use. Function_num Function 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
Arg2	Required	Double	Option - A numerical value that determines which values to ignore in the evaluation range for the function. Option Behavior 0 or omitted Ignore nested SUBTOTAL and AGGREGATE functions 1 Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions 2 Ignore error values, nested SUBTOTAL and AGGREGATE functions 3 Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions 4 Ignore nothing 5 Ignore hidden rows 6 Ignore error values 7 Ignore hidden rows and error values
Arg3	Required	Range	Ref1 - The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.
Arg4 - Arg 30	Optional	Variant	Ref2 - Ref30 - Numeric arguments 2 to 30 for which you want the aggregate value.

Return value

Double

Remarks

The following constraints apply to the Ref arguments (Arg3 - Arg 30) based on the Function_num value.

Function_num	Ref1	Ref2	Ref3, Ref4, ?
1-13	Valid types: Any cell reference Unions Intersections Defined names Structured references Invalid types: Actual data Arrays	Valid types: Any cell reference Unions Intersections Defined names Structured references Invalid types: Actual data Arrays	Valid types: Any cell reference Unions Intersections Defined names Structured references Invalid types: Actual data Arrays
14-17	Valid types: Any cell reference Unions Intersections Defined names Structured references Actual data Arrays	Valid types: Any cell reference Unions Intersections Defined names Structured references Actual data Arrays	No references are allowed

If a second ref argument is required but not provided, Aggregate returns a #VALUE! error.

If one or more of the references are 3D references, Aggregate returns the #VALUE! error value.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.