Count Visible Filtered Excel Table Rows by Expiry Date in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that counts only visible rows of a filtered Excel table based on an expiry date, because COUNTIFS includes hidden rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with an Excel table that has an Expiry Date column and has applied filters. They need a count that changes when rows are hidden or shown, based on whether the expiry date is today or within a date range.
- Observed behavior
- COUNTIFS returns the same result after filtering because it includes hidden rows, so it does not meet the visible-row counting requirement.
Make sure your data range is formatted as an Excel table and note the table name and column name, such as Table1[Expiry Date], because the formulas use structured references.
Count visible filtered rows by expiry date with a SUBTOTAL helper column and SUMPRODUCT
Use this table-based method when you want an easy-to-audit formula that updates automatically after filtering.
COUNTIFS scans every row in the referenced range, including rows hidden by a filter. Adding a helper column based on SUBTOTAL(103,...) returns 1 for visible rows and 0 for hidden rows, so SUMPRODUCT can multiply visibility by your date conditions.
Click the first data cell in a new column of the table, type =SUBTOTAL(103,[@[Expiry Date]]), and press Enter. WPS Spreadsheets fills the formula down the column.
Select the cell where you want the count, enter =SUMPRODUCT(Table1[Visible],--(Table1[Expiry Date]>=TODAY())), and press Enter.
For a rolling six-month count, enter =SUMPRODUCT(Table1[Visible],--(Table1[Expiry Date]>=TODAY()),--(Table1[Expiry Date]<TODAY()+180)) in the target cell.
Count visible filtered rows without a helper column using SUBTOTAL, OFFSET, and SUMPRODUCT
Use this single-formula alternative when you cannot add a helper column and are comfortable with a more complex array-style calculation.
Count visible filtered Excel table rows by expiry date in WPS Spreadsheets
WPS Spreadsheets supports Excel-style tables and formulas, so you can add a SUBTOTAL-based Visible helper and a SUMPRODUCT formula to count only visible rows that match expiry-date criteria.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook containing the filtered table.
- 2. Add the Visible helper column: Click the first cell in a new table column, enter =SUBTOTAL(103,[@[Expiry Date]]), and press Enter.
- 3. Enter the SUMPRODUCT count formula: Select the target cell, enter =SUMPRODUCT(Table1[Visible],--(Table1[Expiry Date]>=TODAY())), and press Enter.
- 4. Apply the date filter and verify: Click the table filter arrow, set the desired date filter, and confirm that the formula counts only visible rows.
- 5. Use the six-month variation if needed: For a rolling 180-day view, enter =SUMPRODUCT(Table1[Visible],--(Table1[Expiry Date]>=TODAY()),--(Table1[Expiry Date]<TODAY()+180)).
Frequently Asked Questions
Why does COUNTIFS return the same value after filtering an Excel table?
COUNTIFS evaluates all rows in the referenced range, including rows hidden by filters. To count visible rows, you need a helper column with SUBTOTAL(103,...) and then SUMPRODUCT.
What does SUBTOTAL(103,...) do in the Visible helper column?
Function number 103 uses COUNTA, which counts only visible non-empty cells. When a row is hidden by a filter, the SUBTOTAL result for that row becomes 0.
How do I count only filtered rows expiring within the next six months?
Use =SUMPRODUCT(Table1[Visible],--(Table1[Expiry Date]>=TODAY()),--(Table1[Expiry Date]<TODAY()+180)) after creating the Visible helper column.
Can I avoid adding a helper column?
Yes, you can use a single SUMPRODUCT formula with SUBTOTAL and OFFSET, but the helper-column approach is easier to audit and update.
Does this method work in WPS Spreadsheets with .xlsx files?
Yes, WPS Spreadsheets supports Excel structured references, SUBTOTAL, SUMPRODUCT and .xlsx files, so the formulas work the same.
