SUBTOTAL Function (OpenOffice Calc)
The SUBTOTAL function in OpenOffice Calc performs calculations on filtered or visible data. Learn syntax, function codes, examples, common errors, and best practices.
Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked
Availability and version history
- Introduced: Documented by Apache OpenOffice 4.1.16; original introduction not established.
- Availability: Supported in Apache OpenOffice 4.1.16, verified against its function registry. This identifies a supported release, not a final working version.
- Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
- Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The SUBTOTAL function in OpenOffice Calc performs calculations on filtered or visible data, making it essential for reports, dashboards, and any dataset where rows may be hidden or filtered. Unlike standard functions such as SUM or COUNT, SUBTOTAL can ignore hidden rows, giving you accurate totals that reflect only what the user sees.
What the SUBTOTAL Function Does
- Performs calculations on visible or filtered rows
- Supports 11 different operations (SUM, AVERAGE, COUNT, MAX, MIN, etc.)
- Can include or exclude manually hidden rows
- Works with AutoFilter and Standard Filter
- Ideal for dashboards and interactive reports
- Prevents double‑counting when data is collapsed or grouped
SUBTOTAL is the preferred function when working with filtered lists or when you need totals that update dynamically as filters change.
Syntax
SUBTOTAL(function_code; range)
Arguments:
- function_code — A number that specifies the operation
- range — The cells to calculate
Function codes 101–111 ignore manually hidden rows.
Function Codes
Codes that include manually hidden rows
- 1 — AVERAGE
- 2 — COUNT
- 3 — COUNTA
- 4 — MAX
- 5 — MIN
- 6 — PRODUCT
- 7 — STDEV
- 8 — STDEVP
- 9 — SUM
- 10 — VAR
- 11 — VARP
Codes that ignore manually hidden rows
- 101 — AVERAGE
- 102 — COUNT
- 103 — COUNTA
- 104 — MAX
- 105 — MIN
- 106 — PRODUCT
- 107 — STDEV
- 108 — STDEVP
- 109 — SUM
- 110 — VAR
- 111 — VARP
Basic Examples
Sum only visible rows
=SUBTOTAL(9; A1:A100)
Function code 9 = SUM.
Count visible rows
=SUBTOTAL(2; A1:A100)
Counts numeric values in visible rows.
Average visible rows
=SUBTOTAL(1; A1:A100)
Ignore manually hidden rows
=SUBTOTAL(109; A1:A100)
Function code 109 = SUM (ignore manually hidden rows).
Advanced Examples
Subtotal across filtered data
=SUBTOTAL(9; B2:B500)
Automatically updates when filters change.
Subtotal across sheets
=SUBTOTAL(9; Sheet1.A1:A200)
Subtotal with mixed hidden and filtered rows
=SUBTOTAL(109; A1:A100)
This version ignores:
- Filtered rows
- Manually hidden rows
Subtotal for grouped data (outline view)
=SUBTOTAL(9; A2:A50)
When groups are collapsed, hidden rows are excluded.
Subtotal inside a table-like structure
=SUBTOTAL(3; C2:C200)
Counts non‑empty visible cells.
Prevent double‑counting in nested subtotals
OpenOffice Calc automatically prevents nested SUBTOTALs from being included in higher‑level SUBTOTALs.
Example:
=SUBTOTAL(9; A1:A100)
If A1:A100 contains other SUBTOTAL formulas, they are ignored.
Common Errors and Fixes
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Incorrect argument separators
- Using commas instead of semicolons
Err:504 — Parameter error
Occurs when:
- Function code is invalid
- Range is malformed
- A non‑numeric function code is used
SUBTOTAL returns unexpected values
Possible causes:
- Using a function code that includes hidden rows
- Using a function code that excludes hidden rows
- Filters not applied correctly
- Hidden rows created by grouping vs manual hiding
Fix: Choose the correct function code (9 vs 109, etc.)
SUBTOTAL includes other SUBTOTAL formulas
OpenOffice Calc normally prevents this, but issues may occur if:
- SUBTOTAL is wrapped inside another function
- SUBTOTAL is used with array formulas
Best Practices
- Use 109 instead of 9 when you want to ignore manually hidden rows
- Use SUBTOTAL instead of SUM for filtered lists
- Avoid mixing SUBTOTAL with array formulas
- Use named ranges for cleaner formulas
- Use SUBTOTAL for dynamic dashboards and reports
- Use grouping (Data → Group and Outline) for collapsible sections