Function Problems

How to Use AGGREGATE to Ignore Errors and Hidden Rows in Excel

Huda Qurayshi Oct 10, 2026 875 views

Question details

The user needs an Excel formula to perform calculations that ignores error values while simultaneously respecting data filters and hidden rows.

How to Use AGGREGATE to Ignore Errors and Respect Filters

Product: Excel
Device & OS: not provided
Scenario: Calculating data sets that contain both error values and filtered or hidden rows, without returning an error or calculating the hidden data.
Observed behavior: Standard functions either calculate hidden rows or return an error if errors exist in the range. Attempting to use multiple options in AGGREGATE like {5,6} causes separate spilled results instead of a single cohesive solution.

Before you start

Ensure your data range has filters applied and identify the specific calculation (such as SUM, AVERAGE, or MAX) you want to perform on the visible, error-free cells.

Solution 1Recommended

Use AGGREGATE with Option 7

Apply the AGGREGATE function using parameter 7, which is specifically designed to ignore both hidden rows and error values simultaneously.

The AGGREGATE function is a powerful alternative to SUBTOTAL. It allows you to apply various aggregate calculations while specifying exactly what types of data to ignore. Option 7 is the correct parameter to use when you need to exclude hidden rows, error values, and nested SUBTOTAL or AGGREGATE functions all at once.

Start the AGGREGATE formula

Click on the cell where you want the calculation result to appear and type =AGGREGATE( to begin the formula.

Select the function number

Enter the number corresponding to your desired calculation (for example, type 9 for SUM or 1 for AVERAGE), followed by a comma.

Apply Option 7

Type 7 as the second argument. This tells the formula to ignore hidden rows and error values. Add another comma.

Select your data array

Highlight the range of cells you want to calculate, close the parenthesis ), and press Enter. For example: =AGGREGATE(9, 7, A2:A20).

Filter Compatibility: As you apply or change filters on your data range, the AGGREGATE formula will automatically update to reflect only the visible rows without breaking due to errors.

Powerful Spreadsheet Calculations

Calculate Complex Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like AGGREGATE, allowing you to easily manage large datasets, ignore errors, and work with filtered tables seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the data.
2. Insert the AGGREGATE Formula: Select a cell and input =AGGREGATE(9, 7, Range) to sum only the visible, error-free cells.
3. Apply Data Filters: Go to the Data tab, select Filter, and hide specific rows to see the AGGREGATE result update dynamically.

Fully compatible with Microsoft Excel formulas, including AGGREGATE and SUBTOTAL.Intuitive formula autocomplete and error-checking tools.Easily apply filters and manage complex datasets.Free, lightweight, and cross-platform Office suite.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a spill error when I try using multiple options like {5,6} in AGGREGATE?

Using an array like {5,6} for the options argument causes the spreadsheet to evaluate the formula multiple times, resulting in a spilled array rather than a single aggregated value. To ignore both hidden rows and errors simultaneously, simply use the single value 7.

What is the difference between AGGREGATE and SUBTOTAL?

While both functions can ignore hidden rows, SUBTOTAL cannot ignore error values within the data range. AGGREGATE offers more control by providing options to ignore hidden rows, errors, and nested subtotal functions, making it more robust for messy datasets.

Which calculations can I perform within the AGGREGATE function?

AGGREGATE supports 19 different functions. The most common include AVERAGE (1), COUNT (2), MAX (4), MIN (5), and SUM (9). It also supports advanced array functions like LARGE (14) and SMALL (15).

Does AGGREGATE ignore manually hidden rows or just filtered ones?

When you use option 3, 5, or 7, the AGGREGATE function ignores both manually hidden rows and rows hidden by standard data filters.

Excel & Data Problems