AGGREGATE Function (LibreOffice Calc)
The AGGREGATE function in LibreOffice Calc performs advanced calculations such as SUM, AVERAGE, COUNT, MAX, MIN, and more, with options to ignore errors, hidden rows, and nested SUBTOTAL/AGGREGATE results. This guide explains syntax, function codes, options, examples, errors, and best practices.
The **AGGREGATE** function is one of the most advanced and flexible functions in LibreOffice Calc. It performs operations such as SUM, AVERAGE, COUNT, MAX, MIN, MEDIAN, LARGE, SMALL, and more—while allowing you to ignore **errors**, **hidden rows**, and **nested SUBTOTAL/AGGREGATE results**.
This guide provides syntax, function codes, options, examples, advanced techniques, common errors, and best practices, making it one of the most complete AGGREGATE references available for LibreOffice Calc.
What the AGGREGATE Function Does
- Performs 19 different operations (SUM, AVERAGE, COUNT, LARGE, SMALL, etc.)
- Can ignore errors, hidden rows, and nested SUBTOTAL/AGGREGATE
- Works with arrays or ranges
- Ideal for filtered tables, dashboards, and error‑prone datasets
- More powerful than SUBTOTAL
AGGREGATE is the visibility‑aware, error‑tolerant version of many core functions.
Syntax
Reference form (most common)
AGGREGATE(function_code; options; range1; range2; ...)
Array form (for LARGE, SMALL, etc.)
AGGREGATE(function_code; options; array; k)
The options argument controls what AGGREGATE ignores.
Function Codes
| Code | Operation |
|---|---|
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV |
| 8 | STDEVP |
| 9 | SUM |
| 10 | VAR |
| 11 | VARP |
| 12 | MEDIAN |
| 13 | MODE |
| 14 | LARGE |
| 15 | SMALL |
| 16 | PERCENTILE.INC |
| 17 | QUARTILE.INC |
| 18 | PERCENTILE.EXC |
| 19 | QUARTILE.EXC |
Options Codes
| Option | Meaning |
|---|---|
| 0 | Ignore nested SUBTOTAL/AGGREGATE |
| 1 | Ignore hidden rows |
| 2 | Ignore error values |
| 3 | Ignore hidden rows + nested SUBTOTAL/AGGREGATE |
| 4 | Ignore error values + nested SUBTOTAL/AGGREGATE |
| 5 | Ignore hidden rows + error values |
| 6 | Ignore hidden rows + error values + nested SUBTOTAL/AGGREGATE |
| 7 | Ignore nothing |
Options 2, 4, 5, and 6 are extremely useful for cleaning error‑ridden datasets.
Basic Examples
Sum visible cells only (ignore hidden rows)
=AGGREGATE(9; 1; A1:A10)
Sum while ignoring errors
=AGGREGATE(9; 2; A1:A10)
Average visible cells only
=AGGREGATE(1; 1; A1:A10)
Count visible numeric cells
=AGGREGATE(2; 1; A1:A10)
Maximum visible value
=AGGREGATE(4; 1; A1:A10)
Array Examples (LARGE, SMALL, etc.)
3rd largest value ignoring errors
=AGGREGATE(14; 2; A1:A10; 3)
5th smallest visible value
=AGGREGATE(15; 1; A1:A10; 5)
Median ignoring hidden rows
=AGGREGATE(12; 1; A1:A10)
Quartile (inclusive) ignoring errors
=AGGREGATE(17; 2; A1:A10; 3)
Advanced Examples
Ignore errors AND hidden rows
=AGGREGATE(9; 6; A1:A1000)
Ignore nested SUBTOTAL/AGGREGATE results
=AGGREGATE(9; 0; A1:A100)
Dynamic dashboards with filters
=AGGREGATE(9; 1; SalesData)
Use AGGREGATE to replace IFERROR + LARGE
=AGGREGATE(14; 2; A1:A100; 1)
Use AGGREGATE to safely compute percentiles
=AGGREGATE(16; 2; A1:A100; 0.9)
Multi‑range summing
=AGGREGATE(9; 1; A1:A10; C1:C10)
Visibility‑aware conditional logic (advanced)
=SUMPRODUCT(SUBTOTAL(103; OFFSET(A1; ROW(A1:A10)-ROW(A1); 0)); B1:B10)
Prevent double‑counting in nested totals
AGGREGATE automatically ignores other AGGREGATE and SUBTOTAL formulas.
Common Errors and Fixes
AGGREGATE returns Err:504
Occurs when:
- Function code is invalid
- Options code is invalid
- Wrong number of arguments
- Array form used incorrectly
AGGREGATE returns wrong result
Possible causes:
- Wrong option code
- Hidden rows included unexpectedly
- Errors not ignored
Fix:
Use option 6 to ignore everything except valid numbers.
AGGREGATE does not ignore hidden rows
Use option 1, 3, 5, or 6.
AGGREGATE does not ignore errors
Use option 2, 4, 5, or 6.
Best Practices
- Use AGGREGATE instead of SUBTOTAL when errors are present
- Use option 6 (ignore hidden + errors + nested totals) for cleanest results
- Use array form for LARGE, SMALL, MEDIAN, QUARTILE, etc.
- Use named ranges for cleaner formulas
- Combine AGGREGATE with SUMPRODUCT for advanced visibility‑aware logic
- Use AGGREGATE in dashboards and filtered tables
AGGREGATE is the most powerful visibility‑aware function in LibreOffice Calc—perfect for real‑world, messy datasets.