In this tutorial, you will learn how to count filtered rows in Excel.
Excel’s SUBTOTAL function, which lets you specify the range from which you wish to count filtered rows and its count feature in the first argument, can be used to count filtered rows. When you use a filter on that range after applying this formula, it will only count the visible rows.
Once you are ready, we can start by using real-life scenarios to help you understand how to count filtered rows in Excel.
Table of Contents
Count Filtered Rows in Excel
Before we begin we will need a group of data to be used to count filtered rows in Excel.
First, you need to have a clean and tidy group of data to work with.
In this example, we are looking for the number of days that classes are held within January 2023. To do so, we will use the filter function to filter out only the dates in January 2023. We will select the entire data group, then select ‘Filter’ to apply a filter on the selected data.
Then, we will select only January 2023 data and press ‘Apply Filter’.
To count the filtered rows, we will use SUBTOTAL function with the following formula =SUBTOTAL(103,A2:A9). The number ‘103’ is to use the COUNTA function to counts all non-empty cells.
Once you press Enter, your formula will return the number of days with classes held.