Documentation/Calc Functions/SUBTOTAL

From The Document Foundation Wiki
Jump to: navigation, search

Other languages:

Function name:

SUBTOTAL

Category:

Mathematical

Summary:

Calculates subtotals. If a range already contains subtotals, these are not used for further calculations. Use this function with the AutoFilters to take only the filtered records into account.

Syntax:

SUBTOTAL(Function; Range)

Returns:

Returns a real value which is the subtotal in a list or database.

Arguments:

Function is an integer number or a reference to the cell containing that integer which stands for one of the following functions:

Function index
(includes hidden values)
Function index
(ignores hidden values)
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

Use numbers 1-11 to include manually hidden rows or 101-111 to exclude them; filtered-out cells are always excluded.

Range is the reference to the cell that are included.

  • If the Number is not in 1-11 or 101-111, the function returns an error value.
  • If any cell in Range doesn't contain numeric value, that cell is ignored.

Additional details:

None

Examples:

You have a table in the cell range A1:B6 containing a bill of material for 10 students. Row 2 (Pen) is manually hidden. You want to see the sum of the figures that are displayed; that is, just the subtotal for the filtered rows. In this case the correct formula would be:

A B
1 ITEM QUANTITY
2 Pen 10
3 Pencil 10
4 Notebook 10
5 Rubber 10
6 Sharpener 10
Formula Description Returns
=SUBTOTAL(9;B2:B6) SUM(9) including manually hidden cells 50
=SUBTOTAL(109;B2:B6) SUM(109) of only non-hidden cells 40

Related LibreOffice functions:

SUM

AVERAGE

ODF standard:

Section 6.16.60, part 2

Equivalent Excel function:

SUBTOTAL