SpreadJS 14
Formula Reference / Formula Functions / Math and Trigonometric Functions / AGGREGATE
In This Topic
    AGGREGATE
    In This Topic

    The AGGREGATE function returns an aggregate in a list or database. Users can apply different aggregate functions like COUNT, AVERAGE, SUM, MAX, MIN etc. to a list while ignoring "error values" and "hidden rows" in the spreadsheets.

    Aggregate function is useful especially when:

    Syntax

    While working with AGGREGATE function, users can use the following syntax:    

    Arguments

    This function has the following arguments:

    Argument Description

     

    Function_num

    [Required], Refers to the number (within the range 1-19) to specify the function that is being used. 

    Note: It is important to note that when "function_num" contains values ranging from 1 to 13, the syntax applicable is : AGGREGATE(function_num, options, ref1, [ref2], …)  and when the "function_num" contains values ranging from 14 to 19, the syntax applicable is AGGREGATE(function_num, options, array, k).

    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
                   
    Options

    [Required], Refers to a particular number that specifies the options to ignore values in 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
    Ref1 [Required], Specifies the reference to a range of cells.
    Ref2 [Optional], Specifies the reference to a range of cells.
    array [Required but only when "function_num" argument above contains values ranging from 14 to 19], Specifies an array, an array formula, or a reference to a range of cells.
    k [Required], This argument is required to specify the functions when the "function_num" values are ranging from 14-19.

    Note: The following things must be kept in mind while using the AGGREGATE function:

    • If the arguments array, k or ref1 is not provided, AGGREGATE function will return the #VALUE! error.
    • If argument k is provided as an array, then the first value is used. If argument k is provided as a cell reference with one column, the value in last row is used. ( For e.g. {1,2,3,4}=>1,   A1:A23  => A23, A1:B23 => #VALUE!, A1:C1 => #VALUE)
    • References don't support 3-D reference. The function will returns #VALUE! if 3-D references are used (For e.g: "Sheet1:Sheet3!B3" will return #VALUE error.).
    • Ignore hidden doesn't support hidden columns.
    • The rows hidden by filter/ hide/group operations will support the ignore feature.
    • If the array includes a calculation, AGGREGATE function will not ignore hidden rows. (eg: "=AGGREGATE(14,3,A1:A6*123,1)")

    Data Types

    Accepts a list or a database of numeric values. Returns aggregate calculation like AVERAGE, COUNT, MIN, MAX etc. while ignoring hidden rows and errors.

    Examples

    The following image depicts four different examples of the AGGREGATE function:

    Let's say you have a spreadsheet with some values in column A and the aggregate functions are applied to cells B1, B2, B3, and B4 as shown in the column B below.

    Aggregate functions 

    The formula output results are displayed in column C and the description of the formula used is mentioned in column D.

    Clearly, the AGGREGATE formula in the cells B1 and B2 used multiple cell references [Syntax 1 explained above] but the formula in the cells B3 and B4 used the array form of the function [Syntax 2 explained above].