Table of Contents
Counting the filtered rows in Excel can be done by following a few simple steps. First, apply the desired filter to the data. Then, select the entire filtered data range by clicking on the top left cell and dragging it to the bottom right cell. Next, in the bottom right corner of the Excel window, the number of filtered rows will be displayed. Alternatively, you can also use the SUBTOTAL function to count the filtered rows. This function automatically ignores hidden rows and only counts the visible ones. Simply type “=SUBTOTAL(3,range)” in an empty cell, where “range” is the cell range you want to count, and press enter. This will give you the total number of filtered rows in the specified range. This method is useful when you want to keep the filtered data visible while counting the rows. By following these steps, you can easily and accurately count the filtered rows in Excel.
Count Filtered Rows in Excel (With Example)
The easiest way to count the number of cells in a filtered range in Excel is to use the following syntax:
SUBTOTAL(103, A1:A10)
Note that the value 103 is a for finding the count of a filtered range of rows.
The following example shows how to use this function in practice.
Example: Count Filtered Rows in Excel
Suppose we have the following dataset that shows the number of sales made during various days by a company:
Next, let’s filter the data to only show the dates that are in January or April.
To do so, highlight the cell range A1:B13. Then click the Data tab along the top ribbon and click the Filter button.
Then click the dropdown arrow next to Date and make sure that only the boxes next to January and April are checked, then click OK:
The data will automatically be filtered to only show the rows where the dates are in January or April:
If we attempt to use the COUNT() function to count the number of values in the Date column, it will actually return the count of all of the original values:
Instead, we can use the SUBTOTAL() function:
This function only counts the visible rows.
From the output we can see that there are 5 days that fall in January or April.
Note that in this particular formula we used 103 in the subtotal function, but we could have also used 102:
Here’s the difference between the two:
- 102 uses the COUNT function, which counts only cells containing numbers.
- 103 uses the COUNTA function, which counts all cells that aren’t empty.
Feel free to use the value in your formula that makes sense for your data.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How do I count the filtered rows in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-count-the-filtered-rows-in-excel/
stats writer. "How do I count the filtered rows in Excel?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-count-the-filtered-rows-in-excel/.
stats writer. "How do I count the filtered rows in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-count-the-filtered-rows-in-excel/.
stats writer (2024) 'How do I count the filtered rows in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-count-the-filtered-rows-in-excel/.
[1] stats writer, "How do I count the filtered rows in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I count the filtered rows in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.