Count Visible Rows with Criteria in Excel
- Percentage of all rows with criteria
- %
- Result
- Total rows
- 10 rows
- You entered
- Visible rows
- 7 rows
- You entered
This guide explains how to count visible rows that meet specific criteria in Excel, such as after filtering or hiding rows. You will learn the SUMPRODUCT and SUBTOTAL functions combination, how to set it up, and common pitfalls to avoid.
Formula
The standard formula to count visible rows with criteria is:
=SUMPRODUCT(SUBTOTAL(3, OFFSET(range, ROW(range)-MIN(ROW(range)), 0, 1)), –(criteria_range = “criteria”))
Where:
- range – the range of cells you are checking for visibility (e.g., A2:A100).
- criteria_range – the range where your criteria are checked (e.g., B2:B100).
- criteria – the value or condition you want to match (e.g., “Yes” or “>5”).
- SUBTOTAL(3, …) – counts non-empty cells in the visible range (function 3 = COUNTA, ignoring hidden rows).
- OFFSET – creates a separate reference for each row so SUBTOTAL can evaluate visibility per row.
- — – converts TRUE/FALSE to 1/0.
For a simpler alternative that works in most Excel versions, use =SUMPRODUCT(SUBTOTAL(3, OFFSET(A2:A100, ROW(A2:A100)-MIN(ROW(A2:A100)), 0, 1)), –(B2:B100=”Yes”)).
Worked Example
Suppose you have a list of tasks in column A (A2:A11) and their status in column B (B2:B11). You filter to show only 7 visible rows (f=7). Among those visible rows, 3 have status “Done” (c=3). The calculator above shows:
- Percentage visible with criteria = (3 / 7) * 100 = 42.9%
- Percentage of all rows with criteria = (3 / 10) * 100 = 30.0%
This matches the formula result when you enter the values correctly.
When to Use This Method
Use this technique when you need to count rows that are both visible (after filtering, hiding, or grouping) and meet a specific condition. Common scenarios include:
- Counting filtered orders that are over $100.
- Counting visible employees in a department after applying a filter.
- Counting visible tasks that are marked as “Complete”.
It is especially useful in dashboards and reports where you want dynamic counts that update as filters change.
How to Read the Result
The result is the number of rows that satisfy both conditions: they are visible and they match the criteria. If you get 0, either no rows meet the criteria or all matching rows are hidden. If you get an error like #VALUE!, check that your ranges are the same size and that you entered the formula as an array formula (in older Excel, press Ctrl+Shift+Enter).
Common Mistakes and Limitations
- Mismatched ranges: The visibility range and criteria range must have the same number of rows.
- Hidden rows vs. filtered rows: SUBTOTAL(3) ignores rows hidden by filtering, but not rows hidden manually (right-click > Hide). For manually hidden rows, use function 103 (COUNTA) instead of 3.
- Using COUNTIF on filtered data: COUNTIF counts all rows, including hidden ones, so it won’t give the correct result.
- Array formula entry: In Excel 2019 and earlier, you must press Ctrl+Shift+Enter after typing the formula. In Excel 365, it works as a dynamic array.
- Performance: On very large datasets (tens of thousands of rows), this formula can slow down. Consider using a helper column with SUBTOTAL or a PivotTable.
Alternatives
- SUBTOTAL with COUNTIF: =SUBTOTAL(3, range) counts visible non-empty cells, but it doesn’t handle criteria directly. Combine with SUMPRODUCT as shown.
- PivotTable: Place your criteria field in the filter area and the count field in values; it updates automatically with filters.
- Excel Table with Total Row: Use a structured reference and the SUBTOTAL function in the total row, but again it doesn’t handle criteria directly.
- Power Query: For complex filtering, load data into Power Query, filter rows, and count – but it’s static unless refreshed.
Frequently Asked Questions
Why does my formula return 0 even though I have visible matching rows?
Check that your ranges are the same size and that you entered the formula as an array formula (Ctrl+Shift+Enter in older Excel). Also ensure you are using the correct criteria syntax – for text, use quotes like “Yes”; for numbers, use just the number or a comparison like “>5”.
Can I count visible rows with multiple criteria?
Yes, multiply additional –(criteria_range=”value”) terms inside the SUMPRODUCT. For example: =SUMPRODUCT(SUBTOTAL(3, OFFSET(A2:A100, ROW(A2:A100)-MIN(ROW(A2:A100)), 0, 1)), –(B2:B100=”Yes”), –(C2:C100=”Active”)).
Does this work with manually hidden rows?
By default, SUBTOTAL(3) ignores rows hidden by filtering, but not rows hidden manually. To count manually hidden rows as invisible, replace 3 with 103 (COUNTA) in the SUBTOTAL function.
Is there a simpler way using Excel 365?
In Excel 365, you can use the FILTER function combined with SUBTOTAL: =ROWS(FILTER(A2:A100, (B2:B100=”Yes”) * SUBTOTAL(3, OFFSET(A2:A100, ROW(A2:A100)-MIN(ROW(A2:A100)), 0, 1)))). This is more readable but still requires the OFFSET trick.
Why does my formula show #VALUE! error?
This usually happens when the ranges are not the same size, or when you use a comparison operator incorrectly. Make sure both ranges have the same number of rows and that you are not mixing text and numbers in the criteria.
Can I use this in Google Sheets?
Yes, the same formula works in Google Sheets, but you may need to use ARRAYFORMULA or the newer LAMBDA functions. The SUMPRODUCT approach is compatible.
Related
Formula used
in t | Total rows | rows | 10 | 0 in f | Visible rows | rows | 7 | 0 in c | Rows meeting criteria | rows | 3 | 0 out pct | Percentage visible with criteria | % | (c / f) * 100 | 1 out pct_all | Percentage of all rows with criteria | % | (c / t) * 100 | 1
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.