To determine the number of filtered rows in Excel, leverage the powerful SUBTOTAL function. This function enables you to conduct a standard count on a column while excluding rows that have been filtered out.
In this concise guide, I’ll demonstrate how to utilize the SUBTOTAL function to count filtered rows in Excel, alongside an alternative visual method for promptly identifying the total number of filtered rows.
Counting Filtered Rows Using the SUBTOTAL Function
Consider a dataset with employee names in column A, department names in column B, and reporting manager names in column C.
Data set without the filter:
After applying a filter, rows where the department name is “Finance” are excluded.
Filtered Data set:
To obtain the count of filtered rows, employ the following SUBTOTAL formula:
=SUBTOTAL(3,B2:B10)
This formula yields the count of visible rows after filtering, giving us 6 in this example.
Explanation of the formula:
3as the first argument indicates that the function should count the range specified in the second argument, considering only visible rows.B2:B10is the range for which the count is performed.
Note: Using 3 excludes rows filtered out by the filter, but hidden rows (manually hidden, not filtered) would still be counted. If you hide rows and then filter the data, the hidden rows won’t be counted.
An alternative formula that also ignores hidden rows in an unfiltered dataset is:
=SUBTOTAL(103,B2:B10)
Both 3 and 103 behave the same way in a filtered dataset.
Clarification through a Table
The table below provides a summary of the behaviors of these formulas in various dataset configurations:
| Dataset Configuration | Using 3 | Using 103 |
|---|---|---|
| Rows not hidden and not filtered | Counts all visible rows | Counts all visible rows |
| Rows hidden and not filtered | Hidden rows are counted | Hidden rows are not counted |
| Rows not hidden and filtered | Only visible rows are counted. Filtered-out rows are not counted | Only visible rows are counted. Filtered-out rows and hidden rows are not counted. |
| Rows are hidden and filtered | Only visible rows are counted. Filtered-out rows and hidden rows are not counted. | Only visible rows are counted. Filtered-out rows and hidden rows are not counted. |
In summary, for a filtered dataset, both 3 and 103 yield the same result.
Checking the Count of Filtered Rows in the Status Bar
Notably, the count of filtered rows is also displayed in the status bar, providing a quick visual reference.
Upon applying a filter, the status bar shows the count of visible cells after the filter, such as “6 of 9 records found,” where 6 is the number of visible records post-filtering, and 9 is the total number of records in the dataset.
Note: Hidden rows do not impact this count; it solely reflects the number of records visible after filtering. Manually hiding rows will not affect this count.
In this tutorial, I’ve demonstrated how to use the SUBTOTAL function to count filtered rows in Excel, clarified the distinctions between the two arguments, and highlighted the status bar’s ability to display the count of filtered rows.
