'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Count Only Filtered Rows in Excel with COUNTIF and COUNTIFS
Question details
The user needs to count or calculate percentages for only visible rows in a filtered dataset based on specific conditions, bypassing the default behavior of COUNTIF and COUNTIFS.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Counting specific values or calculating percentages of visible rows after applying a filter to a dataset.
- Observed behavior
- Standard COUNTIF and COUNTIFS formulas evaluate all rows within the specified range, including the rows that have been hidden by a filter, leading to inaccurate results for visible-only data.
Ensure your data range is correctly filtered and identify the exact column you want to evaluate before typing in the complex nested formula.
Use SUMPRODUCT with SUBTOTAL and OFFSET
Since COUNTIF and COUNTIFS cannot ignore hidden rows, you must combine SUMPRODUCT, SUBTOTAL, and OFFSET to evaluate conditions exclusively on visible cells.
The SUBTOTAL function is unique because it can ignore rows hidden by a filter. By combining it with OFFSET, we can pass visible rows one by one to SUMPRODUCT, which then applies your specific criteria (like values >= 4).
Click on the cell where you want the counted result or percentage to be displayed.
To count visible numeric values greater than or equal to 4 in the range C9:C210, type: =SUMPRODUCT(SUBTOTAL(2,OFFSET(C9:C210,ROW(C9:C210)-MIN(ROW(C9:C210)),,1))*(C9:C210>=4))
If you want the percentage instead of a raw count, divide the formula by the total number of visible numeric cells: =SUMPRODUCT(SUBTOTAL(2,OFFSET(C9:C210,ROW(C9:C210)-MIN(ROW(C9:C210)),,1))*(C9:C210>=4))/SUBTOTAL(2,C9:C210)
To count values in a range, such as greater than or equal to 3 but less than 4, multiply the conditions together: =SUMPRODUCT(SUBTOTAL(2,OFFSET(C9:C210,ROW(C9:C210)-MIN(ROW(C9:C210)),,1))*(C9:C210>=3)*(C9:C210<4))
Press the Enter key. The cell will now display the accurate count or percentage based exclusively on your currently filtered data.
Count Filtered Rows Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT, SUBTOTAL, and OFFSET, allowing you to seamlessly calculate visible rows with complex conditions just like in Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
- 2. Apply filters: Navigate to the Data tab and click the Filter button to hide the rows you do not want to evaluate.
- 3. Insert the nested formula: Select an empty cell and paste the combined =SUMPRODUCT(SUBTOTAL(...)) formula adjusted for your specific data range.
- 4. Get precise results: Press Enter to instantly calculate the count or percentage based only on the rows visible on your screen.
Frequently Asked Questions
Why doesn't COUNTIF or COUNTIFS ignore hidden rows?
By design, COUNTIF and COUNTIFS evaluate the entire specified range, regardless of whether rows are hidden manually or via a filter. To conditionally evaluate only visible rows, you must use visibility-aware functions like SUBTOTAL or AGGREGATE combined with SUMPRODUCT.
Can I use this formula to count text values instead of numbers?
Yes. The example formula uses SUBTOTAL(2, ...), where '2' stands for COUNT (numeric values). To count text, change the '2' to '3', which stands for COUNTA (counts non-empty cells). The formula becomes =SUMPRODUCT(SUBTOTAL(3,OFFSET(...))*(C9:C210="YourText")).
Will this formula update automatically if I change the filters?
Yes. The SUBTOTAL function is dynamic. Whenever you change, apply, or clear filters in your dataset, the formula will instantly recalculate to reflect the newly visible rows.
