Function Problems

How to Count Visible Nonblank Cells in Filtered Excel Ranges

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to count visible, nonblank cells (such as student results) in filtered or non-contiguous Excel ranges.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Counting specific data points within a filtered dataset or dynamic Excel Table.
Observed behavior: Standard counting functions like COUNT or COUNTA include hidden rows, requiring specialized functions to count only the visible, nonblank cells after filtering.

Before you start

Ensure your dataset is organized with clear column headers and no completely blank rows within the data area, as this helps prevent filtering and selection errors.

Solution 1Recommended

Use the SUBTOTAL Function for Visible Nonblank Cells

The SUBTOTAL function with function number 103 is the standard and most efficient way to count visible, nonblank cells in a filtered list.

By using the function number 103, SUBTOTAL acts like COUNTA but explicitly ignores any hidden or filtered-out rows.

Apply a filter to your data

Select your data range, navigate to the Data tab on the ribbon, and click 'Filter'.

Filter the rows

Use the dropdown arrows in your column headers to filter your data as needed (e.g., by class or status).

Enter the SUBTOTAL formula

In a cell outside the filtered range (preferably above the headers or below the dataset), type the formula =SUBTOTAL(103, ActualRange), replacing 'ActualRange' with your specific column reference, such as B2:B100.

Calculate the count

Press Enter. The formula will now display the exact count of nonblank cells that are currently visible on your screen.

Understanding Function Number 103: Using 103 instead of 3 tells the SUBTOTAL function to ignore manually hidden rows as well as filtered-out rows, guaranteeing only visible cells are counted.

Solution 2

Convert Data to an Excel Table for Automatic Expansion

Converting your data into an Excel Table ensures that your counting formulas automatically update when new data, like new test scores, is added.

Solution 3

Combine SUMPRODUCT and SUBTOTAL for Specific Conditions

If you need to count visible cells that meet a specific criterion (e.g., counting only 'Pass' results in a filtered list), you can combine SUMPRODUCT with SUBTOTAL.

Manage Data Effectively

Use WPS Spreadsheet to Count and Filter Data Easily

WPS Spreadsheet provides powerful data processing capabilities identical to Microsoft Excel, including advanced filtering, SUBTOTAL, and SUMPRODUCT functions. It allows you to seamlessly manage complex ranges and count visible non-blank cells effortlessly.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the raw data.
2. Apply a filter: Navigate to the Data tab and click 'Filter' to easily sort and hide rows according to your criteria.
3. Calculate visible cells: In an empty cell, type =SUBTOTAL(103, YourRange) and press Enter to instantly see the visible non-blank count.

Fully compatible with Microsoft Excel formulas like SUBTOTAL, SUMPRODUCT, and OFFSET.Intuitive table formatting tools to handle dynamic range calculations seamlessly.Free, lightweight, and offers fast performance even when filtering massive datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIF function counting hidden rows?

The COUNTIF and COUNT functions are designed to evaluate all cells within a specified range, regardless of whether they are visible or hidden by filters. To exclude hidden rows from your count, you must use functions explicitly built for this purpose, such as SUBTOTAL or AGGREGATE.

What is the difference between SUBTOTAL 3 and SUBTOTAL 103?

Function number 3 tells SUBTOTAL to ignore rows hidden by a filter, but it will still count rows that you have manually hidden via right-click > Hide. Function number 103 is stricter; it ignores both filtered-out rows and manually hidden rows, ensuring only the cells currently visible on your screen are counted.

Can I use the AGGREGATE function instead of SUBTOTAL?

Yes, the AGGREGATE function offers similar and even more advanced capabilities. You can use the formula =AGGREGATE(3, 5, YourRange), where 3 represents the COUNTA operation (counting nonblank cells) and 5 tells the function to specifically ignore hidden rows.

Excel & Data Problems