SUBTOTAL Function (LibreOffice Calc)
The SUBTOTAL function in LibreOffice Calc performs calculations such as SUM, AVERAGE, COUNT, MAX, MIN, and more—while optionally ignoring filtered or hidden rows. This guide explains syntax, function codes, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the SUBTOTAL Function Does ▾
- Performs 11 different operations (SUM, AVERAGE, COUNT, MAX, MIN, etc.)
- Can ignore filtered rows
- Can optionally ignore manually hidden rows
- Works with ranges of any size
- Ideal for filtered tables, reports, and dashboards
- Prevents double‑counting when data is hidden
SUBTOTAL is visibility‑aware, unlike SUM or AVERAGE.
Syntax ▾
SUBTOTAL(function_code; range1; range2; ...)
Function codes 1–11 ignore filtered rows but include manually hidden rows.
Function codes 101–111 ignore both filtered and manually hidden rows.
Function codes 101–111 ignore both filtered and manually hidden rows.
SUBTOTAL automatically ignores other SUBTOTAL results to prevent double‑counting.
Function Codes ▾
| Code | Operation | Ignores Hidden Rows? |
|---|---|---|
| 1 | AVERAGE | Filtered only |
| 2 | COUNT | Filtered only |
| 3 | COUNTA | Filtered only |
| 4 | MAX | Filtered only |
| 5 | MIN | Filtered only |
| 6 | PRODUCT | Filtered only |
| 7 | STDEV | Filtered only |
| 8 | STDEVP | Filtered only |
| 9 | SUM | Filtered only |
| 10 | VAR | Filtered only |
| 11 | VARP | Filtered only |
| 101–111 | Same as above | Filtered + manually hidden |
Basic Examples ▾
Sum visible cells only (filtered rows ignored)
=SUBTOTAL(9; A1:A10)
Sum visible + manually hidden rows
=SUBTOTAL(109; A1:A10)
Count visible numeric cells
=SUBTOTAL(2; A1:A10)
Average visible cells
=SUBTOTAL(1; A1:A10)
Maximum of visible cells
=SUBTOTAL(4; A1:A10)
Advanced Examples ▾
Use SUBTOTAL in filtered tables
=SUBTOTAL(9; B2:B500)
Automatically updates as filters change.
Ignore manually hidden rows (outline groups, hidden rows)
=SUBTOTAL(109; C1:C1000)
Multiple ranges
=SUBTOTAL(9; A1:A10; C1:C10)
Dynamic dashboards
Place SUBTOTAL formulas at the top of a filtered table to create live summaries.
Count visible non‑empty cells
=SUBTOTAL(103; A1:A10)
Count visible blank cells (indirect)
=SUMPRODUCT(SUBTOTAL(103; OFFSET(A1; ROW(A1:A10)-ROW(A1); 0)); A1:A10="")
Combine SUBTOTAL with SUMIF logic (advanced)
=SUMPRODUCT(SUBTOTAL(103; OFFSET(A1; ROW(A1:A10)-ROW(A1); 0)); (B1:B10="North") * C1:C10)
Prevent double‑counting
SUBTOTAL ignores other SUBTOTAL results:
=SUBTOTAL(9; A1:A10)
Even if A1:A10 contains SUBTOTAL formulas, they are skipped.
Common Errors and Fixes ▾
SUBTOTAL returns unexpected results
Possible causes:
- Wrong function code
- Using 1–11 when you need 101–111
- Hidden rows counted unexpectedly
Fix:
Use codes 101–111 to ignore manually hidden rows.
SUBTOTAL includes hidden rows unexpectedly
Use:
=SUBTOTAL(109; A1:A10)
SUBTOTAL ignores formulas returning empty strings
This is expected—SUBTOTAL treats them as non‑empty.
Err:504 — Parameter error
Occurs when:
- Function code is invalid
- Range is malformed
- Semicolons are incorrect
Best Practices ▾
- Use 9 for SUM and 109 for SUM ignoring all hidden rows
- Use SUBTOTAL for filtered tables instead of SUM
- Use 101–111 when manually hiding rows
- Avoid mixing SUBTOTAL with regular totals in the same range
- Use named ranges for cleaner formulas
- Combine SUBTOTAL with SUMPRODUCT for advanced visibility‑aware logic
SUBTOTAL is the correct way to calculate totals in filtered tables—SUM, AVERAGE, and COUNT will mislead you.