Documentation/Calc Functions/SUBTOTAL
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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 a reference to the cells which is the range of cells to be included.
- If the Function is not in 1-11 or 101-111, the function returns an error value.
- If any cell in Range doesn't contain a 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:
ODF standard:
Equivalent Excel function:
SUBTOTAL