Count number of visible rows in a filtered list in Excel
To count number of visible rows in a filtered list in Excel, you can apply the SUBTOTAL function to easily get it done.
How to count number of visible rows in a filtered list in Excel?
Supposing you have a filtered data table as the below screenshot shown, to count how many rows are displayed, you can do as follows.
Range: The range you will count visible rows.
How to use this formula?
1. Select a blank cell to output the result.
2. Enter the below formula into it and press the Enter key.
1) When a filter is active in range B3:B15, to count the number of visible rows, you can apply this formula:
2) For a range contains manually hiding rows rather than applying auto filter, to count only visible rows, you can apply this formula:
How this formula works?
The SUBTOTAL function is useful for ignoring hidden items in a filtered list or table. It can be used for performing different calculation such as average, count, max and so on.
Excel SUBTOTAL function
The Excel SUBTOTAL function returns a subtotal in a list or database
The Best Office Productivity Tools
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Please leave your comments in English