Calculation Issues

How to Filter and Calculate Subtotals for Visible Rows in Excel

Huma Ashraf Ch Oct 7, 2026 870 views

Question details

The user needs to filter a dataset, such as a bank statement, and calculate the sum of only the visible rows, excluding any filtered-out or hidden rows.

How to Filter and Calculate Subtotals for Visible Rows in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering bank statement data by category in a CSV file and summing the amounts for the filtered category.
Observed behavior: Standard SUM functions calculate all rows including hidden ones. The goal is to dynamically calculate totals based only on the currently visible rows after applying a filter.

Before you start

Ensure your dataset has clear column headers without any blank rows separating the data, as this will help the filtering tool and subtotal formula work accurately.

Solution 1Recommended

Use the SUBTOTAL Function to Sum Visible Rows

The SUBTOTAL function is specifically designed to perform calculations like summing, averaging, or counting while ignoring rows hidden by a filter.

When managing bank statements or large financial datasets, using the standard SUM function can lead to inaccurate totals because it includes data that has been filtered out. The SUBTOTAL function solves this by dynamically calculating only the visible rows currently displayed on your screen.

Open and Prepare Data

Open your CSV or Excel file and ensure every column has a distinct header. Remove any entirely blank rows within your data range.

Apply Filters

Navigate to the 'Data' tab on the Excel ribbon and click the 'Filter' button. Dropdown arrows will appear on your column headers.

Enter the SUBTOTAL Formula

Click on an empty cell below your data. Enter the formula =SUBTOTAL(9,B2:B100), replacing B2:B100 with the actual cell range of your amount column. The number '9' tells Excel to use the SUM function for visible rows.

Filter by Category

Click the filter dropdown on your category column, select the specific categories you want to view (e.g., 'Groceries' or 'Utilities'), and click 'OK'. The subtotal will automatically update to sum only those visible rows.

Use the SUBTOTAL Function to Sum Visible Rows

Function Number 109 vs 9: Using the number '9' in the formula ignores rows hidden by an AutoFilter but includes manually hidden rows. If you want to ignore both filtered rows and manually hidden rows, use the number '109' instead (e.g., =SUBTOTAL(109,B2:B100)).

Effortless Data Analysis

Easily Filter and Subtotal Data in WPS Spreadsheet

WPS Spreadsheet provides a robust set of formula tools, including the SUBTOTAL function, to help you seamlessly filter and calculate visible data. It is highly compatible with Microsoft Excel formulas, making it the perfect tool for analyzing CSVs and financial statements.

1. Open your file in WPS Spreadsheet: Launch WPS Office, click on 'Spreadsheet', and open your CSV or Excel file containing the data.
2. Enable the Filter feature: Select your header row, navigate to the 'Data' tab, and click the 'AutoFilter' icon to add dropdowns to your columns.
3. Apply the SUBTOTAL formula: In a blank cell below your data column, type =SUBTOTAL(9, [Your Range]) and press Enter.
4. Filter to view results: Use the filter arrows on your headers to select specific data categories; the formula result will update dynamically to show the new total.

Fully compatible with Microsoft Excel formulas like SUBTOTAL and SUMIntuitive Data tab for quick filtering, sorting, and data managementFree, lightweight, and fast-loading for large CSV filesCross-platform support for Windows, Mac, Linux, iOS, and Android

Frequently Asked Questions

Why is my SUM formula including hidden rows after filtering?

The standard =SUM() function is designed to calculate all cells within a specified range, regardless of whether they are hidden or visible. To sum only visible rows, you must use the =SUBTOTAL() function or the =AGGREGATE() function.

Can I use SUBTOTAL to average or count visible rows instead of summing?

Yes. The first argument in the SUBTOTAL function determines the mathematical operation. Use '1' (or '101') to Average, '2' (or '102') to Count numbers, or '3' (or '103') to Count non-empty cells for the visible rows.

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

Function number 9 ignores rows hidden by an AutoFilter but will include rows that you manually hide via the right-click menu. Function number 109 ignores both AutoFiltered rows and manually hidden rows.

Does the SUBTOTAL function work if I format my data as an Excel Table?

Yes, formatting your data as a Table (using Ctrl+T) makes this process even easier. When you check the 'Total Row' option in the Table Design tab, Excel automatically inserts a SUBTOTAL formula at the bottom that adjusts automatically as you filter the table.

Excel & Data Problems