In Excel, COUNTIF is typically used to count cells based on a single criterion, while SUBTOTAL is designed to provide aggregated results (like sums or counts) that can exclude hidden or filtered rows. By combining these two functions, you gain the flexibility of applying counting criteria (via COUNTIF) while also benefiting from filter-aware calculations (via SUBTOTAL). In this blog post, you’ll learn how they can work together to efficiently process visible rows only, handle filtered data, and more.
Understanding COUNTIF and SUBTOTAL
What Is COUNTIF?
COUNTIF counts the number of cells within a range that meet a specified condition.
COUNTIF(range, criteria)
- range: The cells you want to evaluate.
- criteria: The condition each cell must meet (e.g., “Apples”, “>10”, “USA”).
Key Use Cases: Counting text entries, numeric thresholds, or date ranges in large datasets.
What Is SUBTOTAL?
SUBTOTAL calculates a specific aggregate (sum, average, count, etc.) for a range of data. Unlike standard functions like SUM or COUNT, SUBTOTAL can ignore hidden or filtered-out rows if desired.
SUBTOTAL(function_num, ref1, [ref2], ...)
- function_num: A number (1 to 11, or 101 to 111) that determines which function to use and whether to ignore filtered/hidden rows. For example:
- 1 or 101 = AVERAGE
- 2 or 102 = COUNT
- 3 or 103 = COUNTA
- 9 or 109 = SUM
- (etc.)
- ref1, ref2, …: The ranges or references for which you want the subtotal.
Key Use Cases: Filtering data and recalculating sums or counts on only visible rows.
Why Combine COUNTIF and SUBTOTAL?
- Filtered Data Analysis: When filtering a list by country or product, you might still need the power of COUNTIF to count items that meet a certain condition—but only among visible rows.
- Interactive Dashboards: In dynamic reports or dashboards where rows may be hidden, SUBTOTAL ensures your count updates automatically to match only the displayed data.
- Advanced Calculations: Merging these functions can handle more complex criteria (via COUNTIF) while maintaining the filter-awareness of SUBTOTAL.
Different Ways to Use COUNTIF and SUBTOTAL Together
1. Basic Subtotal Counting (All Rows)
A straightforward approach is to use SUBTOTAL with a function_num that corresponds to COUNT (like 2 or 102). Then incorporate COUNTIF if you need a condition on top of it.
Example (basic SUBTOTAL count on Column A):
=SUBTOTAL(2, A2:A100)
- 2 means “COUNT,” but includes hidden rows. If you filter rows, they will be excluded from the count.
- Alternatively, 102 means “COUNT” but ignores rows hidden manually (e.g., via Hide Row).
Use COUNTIF within or alongside SUBTOTAL for more precise conditions.
2. Counting Only Visible Rows That Meet a Condition
One trick is to pair the SUBTOTAL function with a helper column that uses COUNTIF logic. However, a single combined formula can be more elegant:
=SUMPRODUCT( SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0, 1)), --(A2:A100="Apples") )
- SUBTOTAL(103, OFFSET(…)):
- 103 corresponds to COUNTA ignoring hidden rows.
- OFFSET creates references for each row individually. This trick effectively checks if each row is visible (and not filtered out).
- –(A2:A100=”Apples”) converts TRUE/FALSE to 1/0.
- SUMPRODUCT multiplies each row’s visibility (1 for visible, 0 for hidden) by whether it meets the =”Apples” condition (also 1 or 0), then sums up all results.
This approach mimics a “COUNTIF on visible rows only.”
3. Using a Helper Column
If array formulas seem too complex, you can use a helper column to mark rows as 1 if they meet the COUNTIF criterion, and 0 otherwise. Then apply a standard SUBTOTAL on that helper column:
- Helper Column (e.g., Column D):
=IF(A2="Apples", 1, 0)
- Subtotal Formula:
=SUBTOTAL(9, D2:D100)
- 9 is “SUM,” meaning it will add up the 1’s for visible rows only (if the data is filtered).
4. Subtotal with Multiple Conditions
For multiple criteria, you can combine your helper column logic or use more advanced array formulas:
=SUMPRODUCT( SUBTOTAL(103, OFFSET(A2, ROW(A2:A100)-ROW(A2), 0, 1)), --(A2:A100="Apples"), --(B2:B100>50) )
- This counts only visible rows where A=”Apples” AND B>50.
5. COUNTIF + SUBTOTAL for Dynamic Filtering
Another method is to allow Excel’s built-in Filter on the dataset (e.g., using Filter by Country) and then use:
=SUBTOTAL(2, A2:A100)
or
=SUBTOTAL(3, B2:B100)
But to apply a specific criterion like COUNTIF, you either:
- Use a helper column with a condition, then SUBTOTAL.
- Or use the array approach with SUMPRODUCT + SUBTOTAL(103, …).
Download Example File
We’ve assembled a practice file that contains all the example data and formulas described above. By downloading this file, you can follow each step hands-on—experimenting with filters, hiding rows, and customizing criteria to see precisely how COUNTIF and SUBTOTAL work together. Once you open the file, make sure to filter various columns or hide specific rows to watch these formulas update in real time. Feel free to adapt the data to match your own analysis needs!
Common Pitfalls and Best Practices
- Use the Correct function_num: Remember that 1-11 includes hidden rows, while 101-111 ignores manually hidden rows. For filtered rows, standard SUBTOTAL usage (e.g., 2, 3, 9) also ignores filtered out rows.
- Array Formulas: The SUMPRODUCT + SUBTOTAL(103, OFFSET(…)) approach can be powerful but requires an understanding of how array calculations work.
- Helper Columns: If the array formula approach is too complex or slow on large data, using a helper column is often simpler and more transparent.
- Filter vs. Manual Hide: Keep in mind that “filtered” and “manually hidden” rows behave differently with certain function_num values.
- Performance: Large datasets with many array formulas might slow down Excel; consider optimizing or using helper columns in those scenarios.
Conclusion
Learning to combine COUNTIF and SUBTOTAL enables more dynamic and filter-aware counts. Whether you need a simple filtered data count, or advanced multi-criteria checks that only reflect visible rows, these techniques can streamline your Excel reports and analyses:
- Use SUBTOTAL to ignore filtered or hidden rows.
- Nesting with COUNTIF (directly or via helper columns) filters data by condition and by visibility.
- Choose the Right Approach: Array formulas vs. helper columns vs. simple SUBTOTAL references—pick the one that best suits your workflow.
By trying out the downloadable example file, you’ll gain hands-on experience with each method. Soon, you’ll be applying these insights to any filtered or hidden-row scenario in Excel—making your spreadsheets that much more powerful and efficient!
