Function Problems

How to Add Only Visible Cells After Filtering in Excel

Kushani Nimanthika Oct 21, 2026 868 views

Question details

The user needs to calculate the sum of only the visible cells in an Excel dataset without including rows hidden by filters.

How to Add Only Visible Cells After Filtering in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating accurate sums on a filtered dataset.
Observed behavior: Standard AutoSum includes values from both hidden and visible rows, resulting in an incorrect total that does not reflect the currently filtered view.

Before you start

Ensure your dataset is properly formatted with column headers and that you have applied the necessary data filters to display only the records you wish to calculate.

Solution 1Recommended

Use the SUBTOTAL Function to Add Visible Cells

The SUBTOTAL function with the argument '109' is specifically designed to ignore hidden rows and calculate only the visible data on your spreadsheet.

The standard SUM function evaluates the entire specified range, regardless of active filters or hidden rows. By using the SUBTOTAL function with function number 109, you instruct Excel to exclude manually hidden rows as well as rows hidden by active filters.

Select the Total Cell

Click on the empty cell at the bottom of your dataset where you want the filtered sum to appear.

Enter the SUBTOTAL Formula

Type `=SUBTOTAL(109, ` into the formula bar. The number 109 tells Excel to use the SUM operation while ignoring all hidden rows.

Select the Data Range

Highlight the range of cells you want to add (for example, `D2:D50`), then type a closing parenthesis so the formula looks like `=SUBTOTAL(109, D2:D50)`.

Calculate the Result

Press Enter on your keyboard. The cell will now dynamically display the sum of only the visible, filtered cells.

Use the SUBTOTAL Function to Add Visible Cells

Function Number Variations: Using '9' instead of '109' in the SUBTOTAL function will ignore rows hidden by AutoFilter, but will still include rows that you have manually hidden via right-click > Hide. Always use '109' to ensure all hidden rows are excluded.

Solution 2

Use the AGGREGATE Function

The AGGREGATE function is a more robust alternative that can ignore hidden rows as well as error values within your data range.

WPS Office Spreadsheet

Effortlessly Sum Visible Data in WPS Spreadsheet

WPS Spreadsheet provides exactly the same powerful functions like SUBTOTAL and AGGREGATE, allowing you to easily calculate visible cells in filtered lists with a highly familiar interface.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook containing the dataset.
2. Filter your data: Navigate to the Data tab and click 'AutoFilter' to narrow down your visible rows based on your criteria.
3. Apply the SUBTOTAL formula: In the target cell, type `=SUBTOTAL(109, your_range)` and press Enter to instantly get the exact sum of your visibly filtered data.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Provides intelligent AutoFilter and AutoSum tools.Lightweight, fast, and completely free to download.Familiar interface requires no learning curve to switch.

microsoft office alternative - wps office

Frequently Asked Questions

Why does standard AutoSum include hidden cells?

The standard SUM function is strictly programmed to calculate the exact range of cells mathematically specified in the formula. It does not account for the visual state (hidden or visible) of the rows or columns.

Can I copy and paste only the visible cells after filtering?

Yes. Select your filtered data, press Alt+; (semicolon) on your keyboard to highlight only the visible cells, then copy (Ctrl+C) and paste (Ctrl+V) them to your new location.

What is the difference between SUBTOTAL 9 and 109?

Using SUBTOTAL(9, range) ignores rows hidden by AutoFilter but still includes any rows you manually hid. Using SUBTOTAL(109, range) is more comprehensive and completely ignores both filtered-out rows and manually hidden rows.

Excel & Data Problems