Expand Minimize

WorksheetFunction.Aggregate Method (Excel)

Returns an aggregate in a list or database.

Version Added: Excel 2010

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_numFunction
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9SUM
10VAR.S
11VAR.P
12MEDIAN
13MODE.SNGL
14LARGE
15SMALL
16PERCENTILE.INC
17QUARTILE.INC
18PERCENTILE.EXC
19QUARTILE.EXC

Arg2

Required

Double

Options - A numerical value that determines which values to ignore in the evaluation range for the function.

OptionBehavior
0 or omittedIgnore nested SUBTOTAL and AGGREGATE functions
1Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2Ignore error values, nested SUBTOTAL and AGGREGATE functions
3Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4Ignore nothing
5Ignore hidden rows
6Ignore error values
7Ignore 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

  • 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.

Community Additions

ADD
Show:
© 2014 Microsoft