AGGREGATE

  • Updated

Returns an aggregate in a list or database.

Sample Usage

AGGREGATE(4, 6, A1:A11)

Syntax

AGGREGATE(function_code, options, range1, [range2,..])

  • function_code - Returns an aggregate in a list or database.
  • options - Determines how to deal with hidden rows and error values in the evaluation range for the function.
  • range1 - The array or range to consider while calculating the aggregate value.
  • range2 - [OPTIONAL] Additional arrays or ranges to consider while calculating for aggregate value.

Examples

AGGREGATE(4, 6, C1:C11) calculates the maximum value while ignoring error values in the range C1:C11.

Notes

  • Use the following index to choose a function_code.
    • 1 for AVERAGE
    • 2 for COUNT
    • 3 for COUNTA
    • 4 for MAX
    • 5 for MIN
    • 6 for PRODUCT
    • 7 for STDEV.S
    • 8 for STDEV.P
    • 9 for SUM
    • 10 for VAR.S
    • 11 for VAR.P
    • 12 for MEDIAN
    • 13 for MODE.SNGL
    • 14 for LARGE
    • 15 for SMALL
    • 16 for PERCENTILE.INC
    • 17 for QUARTILE.INC
    • 18 for PERCENTILE.EXC
    • 19 for QUARTILE.EXC

 

  • Use the following index to choose options:
    • 0 (default) to ignore nested SUBTOTAL and AGGREGATE functions
    • 1 to ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
    • 2 to ignore error values, nested SUBTOTAL and AGGREGATE functions
    • 3 to ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
    • 4 to ignore nothing
    • 5 to ignore hidden rows
    • 6 to ignore error values
    • 7 to ignore hidden rows and error values