Function Problems

How to Count Visible Rows for a Specific Month in Excel

Guest Writer Oct 8, 2026 869 views

Question details

The user needs to count the number of rows visible after applying a filter, restricted to a specific month and matching other specified criteria (like a 'Passed' status).

How to Count Visible Excel Rows for a Specific Month

Product: Spreadsheet
Device & OS: not provided
Scenario: Analyzing filtered data where standard counting functions fail to ignore hidden rows, requiring a combination of date ranges and visibility checks.
Observed behavior: The COUNTIFS function counts all rows matching the criteria regardless of whether they are hidden by a filter or visible, leading to inaccurate totals for filtered datasets.

Before you start

Ensure your dataset is organized with clear column headers and that your dates are formatted as actual date values rather than plain text. Identify the exact cell ranges for your date column and criteria column before building the formula.

Solution 1Recommended

Use a Dynamic Array Formula (BYROW and SUBTOTAL)

Leverage newer dynamic array functions to test for visibility, date range, and additional criteria in a single, robust formula.

This method combines the BYROW and LAMBDA functions with SUBTOTAL to create an array of 1s and 0s representing visible and hidden rows. It then mathematically multiplies this array by your date limits (using EOMONTH) and any other conditions to output the final count.

Identify your criteria cells

Identify the reference cell containing the start date of your target month (e.g., E19). Make sure this cell contains a valid date format.

Select the result cell

Click on the cell where you want the final visible count to appear.

Enter the dynamic formula

Type the following formula, adjusting the ranges to match your specific dataset: =SUM((BYROW(F2:F13,LAMBDA(a,SUBTOTAL(103,a))))*(E2:E13>=E19)*(E2:E13<=EOMONTH(E19,0))*(F2:F13="No")).

Calculate the result

Press Enter to calculate. The formula evaluates visibility alongside the condition that the date is greater than or equal to the start date and less than or equal to the end of the month.

Use a Dynamic Array Formula (BYROW and SUBTOTAL)

Version Compatibility: This solution requires an Office version that supports dynamic array functions (such as BYROW and LAMBDA). For older versions, consider using the helper column method.

Solution 2

Use a Helper Column with SUBTOTAL

A highly compatible approach for all spreadsheet versions utilizing an extra column to evaluate conditions before summing visible results.

Advanced Data Analysis in WPS Spreadsheet

Perform Complex Data Counts Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, LAMBDA functions, and SUBTOTAL logic, allowing you to seamlessly calculate visible filtered data. It provides an intuitive, highly compatible environment to handle complex data analysis efficiently.

1. Open your dataset: Launch WPS Spreadsheet and open your document containing the filtered data.
2. Apply your formula: Select the desired cell and input the BYROW or SUBTOTAL formula tailored to your date and criteria ranges.
3. Filter and calculate: Apply your data filters; the formula will dynamically update to count only the visible rows for the specified month.

Fully compatible with Microsoft Excel formulas, including SUBTOTAL and EOMONTH.Built-in support for modern dynamic arrays like BYROW and LAMBDA.Free, lightweight, and fast alternative for professional data processing.Familiar user interface makes migrating your existing spreadsheets seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTIFS include hidden rows?

The COUNTIFS function is designed to evaluate the physical range of data and the criteria provided, independent of the worksheet's visual state. It lacks an internal parameter to differentiate between visible and filtered-out rows.

What does the 103 argument in the SUBTOTAL function mean?

The first argument in SUBTOTAL dictates the operation type and visibility rules. The number '103' tells the function to perform a COUNTA (count non-blank cells) while strictly ignoring hidden rows, whether they are hidden manually or by a filter.

Can I count visible rows without BYROW in older versions?

Yes. Instead of BYROW, you can use the SUMPRODUCT function combined with OFFSET and SUBTOTAL to create an array of visible rows internally, or simply rely on the helper column method which is compatible with all legacy spreadsheet versions.

Excel & Data Problems