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.
Returns a real value which is the subtotal in a list or database.
Function is an integer number or a reference to the cell containing that integer which stands for one of the following functions:
(includes hidden values)
(ignores hidden values)
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.
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:
|=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:
Equivalent Excel function: