How to Make COUNTIFS Respond to Filtered Table Rows in Excel
Question details
The user wants to count rows in a data table that match specific criteria but needs the count to exclude filtered-out rows, ensuring that linked charts update dynamically when the table is filtered.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering a large Excel data table that serves as the source for a dashboard chart, where the summary formulas need to reflect only the currently visible rows.
- Observed behavior
- The standard COUNTIFS function counts all matching rows in the specified range regardless of whether they are visible or filtered out, causing linked charts to remain static.
Ensure your dataset is formatted as a formal Table (Insert > Table) and named properly (e.g., Table1) so that structured column references in the formula work seamlessly.
Combine SUMPRODUCT, SUBTOTAL, and OFFSET Functions
Since COUNTIFS cannot natively ignore hidden or filtered rows, you can use a combination of SUMPRODUCT, SUBTOTAL, and OFFSET to evaluate your criteria against only the visible cells in a range.
This workaround relies on the SUBTOTAL function's ability to identify visible rows. By using OFFSET to feed the rows one by one into SUBTOTAL, and then using SUMPRODUCT to apply multiple criteria to those rows, you can accurately count only the data currently visible on your screen.
Click on the cell where you want to display the dynamic count output (e.g., the cell feeding your dynamic chart).
Type the formula starting with =SUMPRODUCT. You will need to construct conditional arrays for your criteria, multiplied by the SUBTOTAL/OFFSET visibility check.
Enter a formula like: =SUMPRODUCT(--ISNUMBER(SEARCH("😃",Table1[Answer])),--ISNUMBER(SEARCH("Rate your",Table1[Question])),SUBTOTAL(103,OFFSET(Table1[Answer],ROW(Table1[Answer])-MIN(ROW(Table1[Answer])),0,1)))
Press Enter to calculate the result. Apply a filter to your table; you will see the formula output (and any charts linked to it) update instantly to reflect only the visible rows that meet the criteria.
Build Dynamic Dashboards with WPS Spreadsheet
WPS Spreadsheet provides full support for complex array formulas, including SUMPRODUCT, SUBTOTAL, and OFFSET. You can easily calculate visible-only counts and create dynamic charts that update automatically as you filter your data.
- 1. Open your data file: Launch WPS Spreadsheet and open your Excel workbook (.xlsx) containing the data table.
- 2. Insert the dynamic formula: Select your summary cell and paste the SUMPRODUCT and SUBTOTAL combination formula, referencing your table columns.
- 3. Create a linked chart: Go to the Insert tab, select Chart, and set your formula cell as the chart's data source.
- 4. Filter to see updates: Use the Data > Filter tool on your table columns to hide rows, and watch your charts and formula outputs update in real time.
Frequently Asked Questions
Why doesn't COUNTIFS ignore filtered rows natively?
By design, standard conditional counting and summing functions (like COUNTIF, COUNTIFS, SUMIF, and SUMIFS) evaluate every cell in the specified range, whether the rows are visible, manually hidden, or filtered out. Only specific functions like SUBTOTAL and AGGREGATE have built-in parameters to ignore hidden rows.
What does the number '103' mean in the SUBTOTAL function?
In the SUBTOTAL function, the first argument determines the type of operation. The number '3' stands for the COUNTA function (counting non-blank cells). Adding '100' to it (making it '103') instructs the function to ignore hidden and filtered rows during the count.
Will using OFFSET and SUMPRODUCT slow down my workbook?
Yes, it can. The OFFSET function is a 'volatile' function, meaning it recalculates every time any change is made anywhere in the workbook. If applied to extremely large datasets (e.g., hundreds of thousands of rows), this formula combination may cause slight delays in recalculation speeds.
Can I use the AGGREGATE function instead for this task?
Yes, AGGREGATE can be used instead of SUBTOTAL as it also has options to ignore hidden rows. However, to count based on multiple text criteria across arrays, it still generally requires complex array multiplication similar to the SUMPRODUCT method.
