Documentation/Calc Functions/SUBTOTAL

    From The Document Foundation Wiki

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    Other languages:


    Function name:

    SUBTOTAL

    Category:

    Mathematical

    Summary:

    Calculates a subtotal using a selected mathematical function. SUBTOTAL is intended for use with a simple table of data occupying a rectangular spreadsheet area, with the data organized vertically in columns.

    If the data range processed by SUBTOTAL already contains any subtotals, these are ignored to avoid counting values twice. If any row within the data range is filtered out, its data is ignored by SUBTOTAL. SUBTOTAL can optionally include or exclude data from rows within the data range that have been manually hidden.

    Note pin.svg

    Note:
    Calc provides a powerful and more comprehensive alternative to the SUBTOTAL function, accessed by selecting Data ▸ Subtotals on the Menu bar. More details can be found in the system help by searching for "subtotals tool".

    Syntax:

    SUBTOTAL(Function; Range)

    Returns:

    Returns a number that is the required subtotal - see the page that describes the selected subtotal function for more details about the nature of the returned value in each case.

    Arguments:

    Function is an integer, or a reference to a cell containing that integer, that is an index indicating which function should be used for calculating the subtotal and whether hidden rows should be included or excluded. Function must lie either in the range [1, 11] or in the range [101, 111], with the meaning of each possible value given in the following table.

    Mapping of Function argument values
    Include hidden rows Exclude hidden rows Subtotal function
    1 101 AVERAGE
    2 102 COUNT
    3 103 COUNTA
    4 104 MAX
    5 105 MIN
    6 106 PRODUCT
    7 107 STDEV
    8 108 STDEVP
    9 109 SUM
    10 110 VAR
    11 111 VARP

    Range gives the set of real numbers for which the subtotal is to be calculated. Range will usually take one of the following forms:

    • A simple reference to a cell range containing real numbers (for example, A1:A25).
    • The name of a named range, comprising cells containing real numbers.
    • The name of a database range, comprising cells containing real numbers.

    In addition, the following forms for the Range argument do not cause an error but are not likely to be particularly useful:

    • A real number, or an expression that evaluates to a real number. For example, the formula =SUBTOTAL(1; 3) returns 3.
    • A reference to a single cell containing a real number. For example, if cell D1 contains the number 123, the formula =SUBTOTAL(1; D1) returns 123.
    • An inline array of real numbers (such as {1.1; 2.2; 3.3; 4.4}). For example, the formula =SUBTOTAL(6; {1; 2; 3; 4; 5}) returns 120.

    The following conditions (including errors) may be encountered:

    • If Function in non-numeric, then SUBTOTAL reports a #VALUE! error.
    • If Function is a non-integer value, then SUBTOTAL truncates it to an integer.
    • If Function is neither in the range [1, 11] nor in the range [101, 111], then SUBTOTAL reports an invalid argument error (Err:502).

    Additional details:

    • The syntax given in the #Syntax: section above is aligned with that in Calc's Function Wizard. However, it is possible to provide multiple ranges to SUBTOTAL by passing the second range as the third argument, the third range as the fourth argument, and so on. This provides for interoperability with Microsoft Excel.
    • Hiding columns has no impact on the results produced by the SUBTOTAL function.
    • See their individual pages for more details about each of the eleven possible subtotal functions.

    Examples:

    A class of 12 students were set a module test and their scores are entered in a spreadsheet as shown in the table below.

    A B
    1 Student Score
    2 Ann 2
    3 Brian 10
    4 Edward 8
    5 James *
    6 Jeffrey 5
    7 Linette 10
    8 Monica 8
    9 Ray 3
    10 Robert 4
    11 Tamara 6
    12 Victoria 7
    13 William 9


    The students' names are listed in alphabetical order in cells A2:A13 and their scores are listed in cells B2:B13. The asterisk (*) in cell B5 indicates that James was absent and unable to take the test. The following examples are based on the data in the above table.

    Formula Description Returns
    =SUBTOTAL(2; B2:B13)

    Use the COUNT function to count the number of scores. COUNT ignores strings, so the asterisk in cell B5 is ignored. As there are no hidden rows in the data, the formula =SUBTOTAL(102; B2:B13) returns the same value.

    		11
    =SUBTOTAL(3; B2:B13)

    Use the COUNTA function to count the number of scores. COUNTA includes strings, so the asterisk in cell B5 is counted. As there are no hidden rows in the data, the formula =SUBTOTAL(103; B2:B13) returns the same value.

    		12
    =SUBTOTAL(4; B2:B13)

    Use the MAX function to determine the maximum score value. As there are no hidden rows in the data, the formula =SUBTOTAL(104; B2:B13) returns the same value.

    		10
    =SUBTOTAL(5; B2:B13)

    Use the MIN function to determine the minimum score value. As there are no hidden rows in the data, the formula =SUBTOTAL(105; B2:B13) returns the same value.

    		2

    For the following examples the same data table is used but row 2 is hidden (by right-clicking on the row number and selecting Hide Rows from the context menu).

    Formula Description Returns
    =SUBTOTAL(2; B2:B13)

    Use the COUNT function to count the number of scores. COUNT ignores strings, so the asterisk in cell B5 is ignored. In this case, SUBTOTAL includes values in hidden rows.

    		11
    =SUBTOTAL(102; B2:B13)

    Use the COUNT function to count the number of scores. COUNT ignores strings, so the asterisk in cell B5 is ignored. In this case, SUBTOTAL ignores values in hidden rows.

    		10
    =SUBTOTAL(5; B2:B13)

    Use the MIN function to determine the minimum score value. In this case, SUBTOTAL includes values in hidden rows.

    		2
    =SUBTOTAL(105; B2:B13)

    Use the MIN function to determine the minimum score value. In this case, SUBTOTAL ignores values in hidden rows.

    		3

    For the final examples, the data table is reverted to its original state by unhiding row 2 (by selecting the row numbers for rows 1 and 3, right-clicking on either of the selected row numbers, and selecting Show Rows from the context menu). Then AutoFilters are applied by left-clicking within the cell range A1:B13 and then selecting Data ▸ AutoFilter on the Menu bar. Finally, press the down arrow in the Score column heading, untick the check box next to the number 10 near the bottom of the dialog, and press the Accept button. Rows 3 and 7 are now filtered out.

    Formula Description Returns
    =SUBTOTAL(2; B2:B13)

    Use the COUNT function to count the number of scores. COUNT ignores strings, so the asterisk in cell B5 is ignored. As there are no hidden rows in the data, the formula =SUBTOTAL(102; B2:B13) returns the same value.

    		9
    =SUBTOTAL(3; B2:B13)

    Use the COUNTA function to count the number of scores. COUNTA includes strings, so the asterisk in cell B5 is counted. As there are no hidden rows in the data, the formula =SUBTOTAL(103; B2:B13) returns the same value.

    		10
    =SUBTOTAL(4; B2:B13)

    Use the MAX function to determine the maximum score value. As there are no hidden rows in the data, the formula =SUBTOTAL(104; B2:B13) returns the same value.

    		9

    Related LibreOffice functions:

    AVERAGE

    COUNT

    COUNTA

    MAX

    MIN

    PRODUCT

    STDEV

    STDEVP

    SUM

    VAR

    VARP

    ODF standard:

    Section 6.16.60, part 2

    Related (or similar) Excel functions:

    SUBTOTAL


    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/Calc_Functions/SUBTOTAL&oldid=733299"