How to count filtered rows in Excel
By
SpreadCheaters
You can watch a video tutorial here.
In Excel, filters are a popular way of creating a subset of the data to analyze it or to perform data cleaning operations. As part of data analysis, you may need to understand how many rows are returned as a result of applying a filter. You can do so using the SUBTOTAL() function and a filter.
1. SUBTOTAL() function: this returns the subtotal for a column using a specified aggregation function
a) Syntax: SUBTOTAL(function_code, ranges)
i. function_code: this is a number that corresponds to an aggregation function e.g. 1 is AVERAGE, 2 is COUNT, etc.
ii. ranges: the range of the cells on which the function is to be applied
Step 1 – Create the Subtotal() function
– Select a cell below the data to be filtered
– Type the formula using cell references:
=SUBTOTAL(3, <range of the first column>)
Note: 3 represents the COUNTA function that is used to count both numbers and text
Step 2 – Turn on the in-column filters
– Select any table header
– Go to Data > Sort & Filter
– Click the Filter button
Step 3 – Apply the filter
– Expand the filter drop-down menu of the first column
– Click Select All to unselect all options
– Click ‘Monthly A’
– Click OK
Step 4 – Check the count
– On the bottom left of the sheet, the number of filtered cells will be displayed
– Select the filtered cells and check the bottom left of the sheet where the count of the cells will be displayed
– These numbers will match the display of the SUBTOTAL() function
