AGGREGATE in Collabora Office: Summarize Ranges While Choosing What to Ignore

=AGGREGATE(Function; Option; Number1 [; Number2] … [; Number253])
=AGGREGATE(Function; Option; Array [; k])

Function and Option are required numbers or references to cells holding them. The reference form accepts up to 253 numeric arguments. The array form takes a range, named range, or supported column label. Automatic column/row label recognition must be enabled to use column labels as the help describes.

For Function codes 14–19, k is required and has the meaning of the selected function’s second argument: rank, percentile, or quartile. Do not treat k as another ordinary number to aggregate.

The word “only” matters: option 6 ignores errors but can still count nested totals. It is not interchangeable with option 2.

Enter 10 in A2, 20 in A3, =1/0 in A4, 30 in A5, =SUBTOTAL(9;A2:A3) in A6, and 40 in A7. A6 has independent numeric value 30.

=AGGREGATE(9;2;A2:A7) has documentation-based independent expectation 100, adding 10+20+30+40 while ignoring the error and nested subtotal. Changing Option to 6 gives independent expectation 130, because the nested 30 is now included.

SUBTOTAL is the related nested-total function. This deliberate fixture shows why a plausible sum can still double-count summary rows if the wrong option is selected.

For the same data, use:

=AGGREGATE(14;2;A2:A7;3)

The independent result is 20, the third largest among 10,20,30,40 after excluding the error and nested subtotal. With option 6, the included numbers become 10,20,30,30,40, so the third largest is 30.

This is a meaningful difference even though both options suppress the visible error. LARGE explains the rank interpretation; k=3 asks for third largest, not the top-three sum.

The Collabora section says AGGREGATE is intended for vertical ranges with AutoFilter activated. Without AutoFilter, automatic recalculation does not work for newly hidden rows. Do not promise that hiding a row alone always refreshes the result immediately.

With the fixture above, the documented exclusion model predicts 70 for Function 9/Option 3 if the row containing the ordinary 30 is hidden. Option 2 retains that row and remains 100. These are conditional expectations for the stated hidden-row state, not an observed GUI test.

For horizontal ranges, the help explicitly says hidden columns are not recognized, although errors and nested results can still be omitted. Hiding a column is therefore not the horizontal equivalent of excluding a hidden row.

Check the Function code, Option code, and whether k is required. Confirm the range contains detail values versus nested totals, and verify the AutoFilter/recalculation condition before diagnosing a hidden-row discrepancy.

The dedicated section does not provide every native error code for invalid function/option/k combinations or every calculated-array edge case. Use valid codes and direct ranges for the reproducible controls. The vendor lists namespace COM.MICROSOFT.AGGREGATE, outside OpenDocument 1.3 OpenFormula; this identifier does not certify behavior in another spreadsheet.

The exact function is documented in Collabora Office 26.04 desktop help, retrieved and reviewed on October 17, 2026. This establishes a documentation scope, not the first release that introduced the function or the last version in which it works; those versions and any retirement date remain unestablished. Collabora Online and mobile support are not inferred from desktop help, nor is behavior in another spreadsheet product.

The examples are documentation-based expectations with independent calculations. They have not been executed in Collabora Office. No application screenshot or native error/coercion certification is implied. Formulas use English function names and semicolon argument separators.

• Collabora Office 26.04: AGGREGATE (checked October 17, 2026).
• Editorial testing standards.