Documentation/Calc Functions/SUBTOTAL

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.

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.

Range gives the set of real numbers for which the subtotal is to be calculated. Range will usually take one of the following forms: In addition, the following forms for the Range argument do not cause an error but are not likely to be particularly useful:
 * 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.
 * A real number, or an expression that evaluates to a real number. For example, the formula  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  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 not in either the range [1, 11] or the range [101, 111], then SUBTOTAL reports an invalid argument error (Err:502).

Additional details:

 * The syntax given in the 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.

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.

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

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

Related LibreOffice functions:
AVERAGE

COUNT

COUNTA

MAX

MIN

PRODUCT

STDEV

STDEVP

SUM

VAR

VARP

ODF standard:
Section 6.16.60, part 2

Equivalent Excel function:
SUBTOTAL