How to Count Only Visible Filtered Rows with Multiple Criteria in Excel
Question details
The user needs to count specific data rows using multiple criteria while exclusively evaluating visible rows and ignoring those hidden by filters.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Counting data matching multiple specific conditions after applying column filters.
- Observed behavior
- Standard functions like COUNTIFS include hidden and filtered-out rows in their calculation, resulting in an inaccurate count of the currently displayed data.
Ensure your data ranges are identical in size across your formulas, and verify that your target criteria are correctly spelled before applying the array formula.
Use SUMPRODUCT combined with SUBTOTAL and OFFSET
This method replaces COUNTIFS by creating an array formula that checks both your specific conditions and the visibility status of each row.
Because COUNTIFS evaluates an entire range regardless of row visibility, you must use a combination of functions to achieve this. The SUBTOTAL function can ignore hidden rows (using argument 103), while OFFSET feeds it one row at a time. SUMPRODUCT then multiplies these visibility results against your custom criteria.
Click on the cell where you want the final visible row count to be displayed.
Type the formula: =SUMPRODUCT(($A$12:$A$400="sample1")*(DM$12:DM$400>0)*SUBTOTAL(103,OFFSET($A$12,ROW($A$12:$A$400)-ROW($A$12),0)))
Modify the range $A$12:$A$400 to match your first data column, and change "sample1" to the specific text or value you want to match.
Modify the range DM$12:DM$400 to match your second data column, and adjust >0 to your desired numerical or text condition.
Press Enter. The formula will immediately calculate the count of rows that meet both criteria and are currently visible.
Efficiently Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including SUMPRODUCT, SUBTOTAL, and OFFSET, allowing you to seamlessly analyze complex, filtered datasets without worrying about formula errors.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the filtered data.
- 2. Apply your data filters: Navigate to the 'Data' tab and use the 'Filter' tool to hide rows you do not want to see.
- 3. Enter the SUMPRODUCT formula: Paste your tailored SUMPRODUCT and SUBTOTAL formula into an empty cell just as you would in standard spreadsheet software.
- 4. Instantly analyze data: Hit Enter to calculate. The results will dynamically update whenever you change your filter settings.
Frequently Asked Questions
Why doesn't the COUNTIFS function ignore filtered rows automatically?
The COUNTIFS function is designed to evaluate the physical contents of the cells within a defined range. It does not possess a built-in mechanism to check the display status (hidden or visible) of those cells, which is why SUBTOTAL is needed.
Does this formula work for rows hidden manually rather than by filters?
Yes. By using the function argument 103 inside the SUBTOTAL formula, it guarantees that both filtered-out rows and manually hidden rows are excluded from the calculation.
Can I use whole column references like A:A in this formula?
It is not recommended. Array formulas utilizing SUMPRODUCT and OFFSET evaluate cell by cell. Using entire column references (e.g., A:A) forces the spreadsheet to calculate over a million rows, which can significantly slow down performance. Always use specific ranges like $A$12:$A$400.
