Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Count the Visible Rows in a Filtered List in Excel
How to use the COUNT function on a filtered list of data so that hidden rows are not included in the count.
To do this, use the SUBTOTAL() function.
Sections:
Example - Exclude Manually Hidden Rows
Syntax
=SUBTOTAL(2, range_to_count)
2 tells the function to perform a count.
range_to_count is the range that you want to count.
COUNTA
Use a 3 instead of a 2 to perform a COUNTA instead of just a regular COUNT.
Example - Filtered Data
=SUBTOTAL(2,B4:B8)
This counts the cells that are still visible, after a filter has been applied, in the range B4:B8.
Result:
This updates each time a filter is added or removed and also works when no filter at all is used:
Look to the next example to see how to avoid counting rows that were manually hidden.
Example - Exclude Manually Hidden Rows
The above example does not work when rows are manually hidden - right-click a row and click Hide to manually hide a row.
To make sure the function does not count rows that were manually hidden, we must change the first argument of the function.
=SUBTOTAL(102,B4:B8)
102 was 2 in the first example. Changing this to 102 makes the function ignore filtered data and also manually hidden data.
Nothing else was changed.
COUNTA
If using the COUTNA feature, change the 3 to 103.
Notes
The COUNT functionality only counts cells that contain numbers.
The COUNTA functionality counts all cells that are not empty.
As such, it may be better for you to use the COUNTA functionality (either 3 or 103) for the SUBTOTAL function.
The SUBTOTAL function allows you to apply a number of regular functions to filtered data, for a full list and explanation, view our tutorial on the SUBTOTAL function in Excel.
Make sure to download the file for this tutorial so you can work with the above examples in Excel.
Question? Ask it in our Excel Forum
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
Tutorial: How to calculate the payment amount for a loan or similar financial instrument that has a ...
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...