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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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