SpreadJS 14
Formula Reference / Formula Functions / Math and Trigonometric Functions / AGGREGATE
In This Topic
    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:


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


    This function has the following arguments:

    Argument Description



    [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
    2 COUNT
    3 COUNTA
    4 MAX
    5 MIN
    7 STDEV.S
    8 STDEV.P
    9 SUM
    10 VAR.S
    11 VAR.P
    12 MEDIAN
    13 MODE.SNGL
    14 LARGE
    15 SMALL

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


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