How to Count Visible Rows by Criteria and Sum Filtered Data in Excel
Question details
The user needs to evaluate filtered data by specific criteria (such as counting 'S' or 'L' in certain columns) and sum values for visible rows only, preferably without using helper columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset and attempting to extract conditional counts and sums based on the visible data only.
- Observed behavior
- Using standard formulas like SUBTOTAL(3, range) incorrectly counts all nonblank cells rather than matching specific text criteria, while standard COUNTIF/SUMIF functions evaluate all rows regardless of visibility.
Ensure your dataset has an AutoFilter applied and check that your target columns do not contain existing formula errors, which might break complex array formulas.
Use SUMPRODUCT with SUBTOTAL and OFFSET
Combine SUMPRODUCT, SUBTOTAL, and OFFSET to evaluate both cell visibility and specific text criteria dynamically without the need for helper columns.
SUBTOTAL on its own cannot check for specific criteria like 'S' or 'L'. By using SUMPRODUCT in combination with SUBTOTAL and OFFSET, you force Excel to check the visibility of each individual row and then multiply it by your condition.
This creates a powerful array formula that effectively acts as a 'COUNTIF Visible' or 'SUMIF Visible'.
Click on the cell where you want to display the final count or sum.
Type the formula: =SUMPRODUCT((F2:F100="S")*(SUBTOTAL(103,OFFSET(F2,ROW(F2:F100)-MIN(ROW(F2:F100)),0)))) and press Enter. Adjust 'F2:F100' to match your actual data range.
To sum filtered values based on criteria, change the SUBTOTAL function number from 103 (COUNTA) to 109 (SUM). For example: =SUMPRODUCT((F2:F100="S")*(SUBTOTAL(109,OFFSET(L2,ROW(L2:L100)-MIN(ROW(L2:L100)),0)))).
Use a Helper Column (Simpler Alternative)
Adding a single helper column to flag visible rows makes writing COUNTIFS and SUMIFS much easier and improves calculation speed.
Utilize a PivotTable
PivotTables automatically aggregate visible data only, completely eliminating the need for complex formulas.
Easily Count and Sum Filtered Data with WPS Spreadsheet
WPS Spreadsheet offers powerful, 100% compatible functions like SUMPRODUCT, SUBTOTAL, and advanced PivotTables to help you analyze filtered data accurately and efficiently.
- 1. Open your dataset: Launch WPS Spreadsheet and open your document.
- 2. Filter your data: Go to the Data tab and apply the AutoFilter to your columns.
- 3. Apply advanced functions: Use the SUMPRODUCT and SUBTOTAL combination formula directly in your target cell, just as you would in Excel.
- 4. Create a PivotTable (Optional): Navigate to Insert > PivotTable to effortlessly count and sum your data visually without complex formulas.
Frequently Asked Questions
Why doesn't the COUNTIF function work on filtered data?
The standard COUNTIF and SUMIF functions evaluate all cells in the specified range, regardless of whether they are hidden or filtered out by AutoFilter. To evaluate only visible cells, you must rely on functions designed to check visibility, such as SUBTOTAL or AGGREGATE.
Can I use AGGREGATE instead of SUBTOTAL for visible rows?
Yes, AGGREGATE is a powerful alternative. Using AGGREGATE with function number 3 (for COUNTA) or 9 (for SUM) and option 5 (ignore hidden rows) achieves similar results to SUBTOTAL, and it can also natively ignore error values within arrays.
What does the OFFSET and ROW combination do in this array formula?
The OFFSET and ROW combination breaks a continuous range into an array of individual cell references. This trick allows the SUBTOTAL function to test the visibility status of every single row one by one, enabling SUMPRODUCT to match that status against your specific text criteria.
