Calculation Issues

How to Make Excel Formulas Recalculate After Filtering Data

Natalie Taylor Oct 14, 2026 872 views

Question details

The user needs aggregation formulas to dynamically update and display results based only on the visible records when a filter is applied.

How to Make Excel Formulas Recalculate After Filtering Data

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering a dataset by specific criteria (such as date or store location) and calculating the sum, average, or count of the filtered results.
Observed behavior: Standard formulas like SUM, AVERAGE, and COUNT continue to include hidden, filtered-out rows in their calculations instead of updating to reflect only visible data.

Before you start

Ensure that your dataset has clear column headers and no blank rows interrupting the data range before you begin writing your formulas and applying filters.

Solution 1Recommended

Use the SUBTOTAL Function for Visible Data

The SUBTOTAL function is specifically designed to ignore filtered-out rows, making it the perfect replacement for standard SUM, AVERAGE, or COUNT functions when working with filtered lists.

By replacing standard aggregation formulas with SUBTOTAL, you ensure that any applied data filters are immediately reflected in your final calculation.

SUBTOTAL uses a specific function number as its first argument to determine which mathematical operation to perform.

Select a calculation cell

Select an empty cell outside of your intended filter range (preferably above the dataset headers) to ensure the formula row itself does not get hidden when you apply a filter.

Start the SUBTOTAL formula

Type =SUBTOTAL( into the formula bar.

Enter the function number

Enter the number corresponding to the calculation you want to perform. For example, type 9 for SUM, 1 for AVERAGE, or 2 for COUNT. Add a comma after the number.

Select the data range

Select the entire range of data you want to calculate, close the parentheses, and press Enter (e.g., =SUBTOTAL(9, C2:C100)). The formula will now dynamically update when you filter the data.

Use the SUBTOTAL Function for Visible Data

Function Numbers Explained: Using function numbers 1-11 will ignore filtered-out rows but include manually hidden rows. If you want to ignore both filtered rows and manually hidden rows, use function numbers 101-111 (e.g., 109 for SUM).

Solution 2

Use the AGGREGATE Function for Advanced Filtering

The AGGREGATE function works similarly to SUBTOTAL but offers additional options, such as the ability to ignore error values alongside hidden rows.

Advanced Spreadsheet Tools

Filter and Calculate Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced data filtering and dynamic calculation functions like SUBTOTAL and AGGREGATE. It provides a lightweight, highly compatible environment to handle complex data analysis efficiently.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the data you want to filter.
2. Apply data filters: Select your data range, navigate to the 'Data' tab in the top ribbon, and click 'Filter'.
3. Insert the SUBTOTAL formula: In an empty cell above your data, type =SUBTOTAL(9, your_range) to calculate the sum of visible cells.
4. Filter your results: Use the drop-down arrows on your column headers to filter your data; the SUBTOTAL formula will automatically recalculate to reflect only the visible rows.

100% compatible with Microsoft Excel formulas, functions, and formattingUser-friendly interface for quick data filtering, sorting, and analysisFree to use with comprehensive data processing tools built-inLightweight application that runs smoothly and quickly on all devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does the SUM function not change when I filter my data?

The standard SUM function calculates all cells in a specified range, regardless of whether they are visible or hidden by a filter. To calculate only the visible cells, you must use the SUBTOTAL or AGGREGATE function.

What is the difference between SUBTOTAL function numbers 9 and 109?

Function number 9 ignores rows that are hidden by a data filter but includes rows that are manually hidden by the user. Function number 109 ignores both filtered rows and manually hidden rows.

Can I use SUBTOTAL to count text values after filtering?

Yes. Use the function number 3 (which corresponds to COUNTA) in your SUBTOTAL formula, such as =SUBTOTAL(3, A2:A50), to count visible cells that contain text or any non-blank values.

Where is the best place to put the SUBTOTAL formula?

It is highly recommended to place the SUBTOTAL formula either above your data headers or in a completely separate summary sheet. Placing it below the data can sometimes result in the formula row being accidentally hidden when you apply filters to the dataset.

Excel & Data Problems