Using AGGREGATE to Ignore Hidden Rows in LibreOffice Calc

The AGGREGATE function in LibreOffice Calc allows you to perform various mathematical and statistical calculations while ignoring hidden rows, error values, or nested subtotal functions. This guide explains how to construct an AGGREGATE formula specifically tailored to exclude rows hidden either manually or by using an AutoFilter.

The AGGREGATE Function Syntax

The standard syntax for the AGGREGATE function is:

AGGREGATE(Function_Index; Option; Reference_1; [Reference_2]; ...)

• Function_Index: A number from 1 to 19 representing the operation you want to perform (e.g., 9 for SUM, 1 for AVERAGE).
• Option: A number from 0 to 7 that defines what data the function should ignore during the calculation.
• Reference_1: The cell range or array containing your data.

Key Options to Ignore Hidden Rows

To make AGGREGATE ignore hidden rows, choose the appropriate Option code:

• 5: Ignores hidden rows only.
• 1: Ignores hidden rows and nested SUBTOTAL or AGGREGATE functions.
• 3: Ignores hidden rows, error values, and nested SUBTOTAL or AGGREGATE functions.
• 7: Ignores hidden rows and error values.

Step-by-Step Examples

1. Summing Visible Rows

To calculate the total sum of visible cells in the range A2:A20 while ignoring rows that are hidden:

=AGGREGATE(9; 5; A2:A20)

• 9 sets the operation to SUM.
• 5 instructs Calc to ignore hidden rows.

2. Averaging Visible Rows While Ignoring Errors

If your range contains potential error values (like #N/A or #DIV/0!) alongside hidden rows, use option 7 to ignore both:

=AGGREGATE(1; 7; B2:B50)

• 1 sets the operation to AVERAGE.
• 7 instructs Calc to ignore both hidden rows and error values.

3. Finding the Maximum Value in Visible Data

To retrieve the largest visible value in range C2:C30:

=AGGREGATE(4; 5; C2:C30)

• 4 sets the operation to MAX.
• 5 instructs Calc to skip hidden rows.

Common Function Index Codes Reference