Partager via

WorksheetFunction.Aggregate Method (Excel)

  • Article

Returns an aggregate in a list or database.

Version Information

Version Added: Excel 2010

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

Options - 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:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays


    Invalid types:

    • Actual data

    • Arrays

    Valid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references


    Invalid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays


    Invalid types:

    • Actual data

    • Arrays

    Valid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references


    Invalid types:

    • Any cell reference

    • Unions

    • Intersections

    • Defined names

    • Structured references

    • Actual data

    • Arrays


    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 3-D references, AGGREGATE returns the #VALUE! error value.

See Also

Concepts

WorksheetFunction Object

WorksheetFunction Object Members