Formula Errors

How to Count Visible Filtered Cells Less Than a Value in Excel

Bushra Parveen Sep 25, 2026 869 views

Question details

The user needs to count the number of visible, non-blank cells containing a value less than 27 in a specific range (I8:I60), while ignoring rows hidden by a filter.

How to Count Visible Cells Less Than 27 After Filtering in Excel

Product: Excel
Device & OS: not provided
Scenario: Performing conditional counts on a dataset where a filter has been applied to hide specific rows.
Observed behavior: The standard COUNTIF function counts all cells in the specified range matching the condition, failing to exclude the rows that are currently hidden by the filter.

Before you start

Ensure your dataset is actively filtered and that there are no merged cells within your target range, as merged cells can cause complex array formulas like SUMPRODUCT and OFFSET to return errors.

Solution 1Recommended

Use SUMPRODUCT with SUBTOTAL and OFFSET

This is the most reliable method for most versions of Excel, using SUBTOTAL to check if each row is visible and SUMPRODUCT to evaluate the numerical condition.

The standard COUNTIF function cannot ignore filtered rows. To solve this, you can combine SUMPRODUCT, SUBTOTAL, and OFFSET to iterate through the visible cells and apply your condition.

Select the output cell

Click on an empty cell where you want the final count to be displayed.

Enter the formula

Type the following formula exactly: =SUMPRODUCT(SUBTOTAL(103,OFFSET(I8:I60,ROW(I8:I60)-MIN(ROW(I8:I60)),0,1)),--(I8:I60<27))

Calculate the result

Press Enter to evaluate the formula. It will now display the total count of visible cells that are less than 27.

How it works: SUBTOTAL(103, ...) acts as a COUNTA function that ignores hidden rows, checking row visibility one by one via OFFSET. The double negative (--) converts the TRUE/FALSE results of the less-than-27 condition into 1s and 0s for SUMPRODUCT to calculate.

Solution 2

Use BYROW and LAMBDA (For Microsoft 365 Users)

A modern and cleaner approach that utilizes dynamic array functions available exclusively in Microsoft 365.

Count Filtered Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, including SUMPRODUCT, SUBTOTAL, and dynamic arrays, allowing you to seamlessly analyze and count filtered datasets.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your data.
2. Apply your data filters: Highlight your headers and go to the Data tab, then click Filter to hide unwanted rows.
3. Input the array formula: Select a summary cell and paste the SUMPRODUCT and SUBTOTAL formula.
4. Get instant results: Press Enter to instantly view your visible cell count without modifying your original dataset.

Fully compatible with Microsoft Excel formulas and functionsFree and lightweight office suite for Windows, Mac, and LinuxAdvanced data filtering and array processing tools built-inFamiliar user interface for seamless workflow migration

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't COUNTIF ignore hidden rows in Excel?

The COUNTIF and COUNTIFS functions are inherently designed to evaluate the entire specified range in the spreadsheet's memory, regardless of whether rows are hidden by filters or manually hidden. To evaluate only visible cells, you must use functions specifically designed to respect filters, such as SUBTOTAL or AGGREGATE.

How do I change the formula to count cells greater than a different number?

You can easily modify the condition segment at the end of the formula. For example, to count visible cells greater than 50, change the segment --(I8:I60<27) to --(I8:I60>50).

What does the 103 stand for in the SUBTOTAL function?

The number 103 is a specific function_num argument within SUBTOTAL. It represents the COUNTA function (which counts non-blank cells) but specifically commands Excel to ignore any rows that have been hidden, whether manually or through a data filter.

Excel & Data Problems