How to Count Filtered Rows in Excel

 This guide will discuss how to count filtered rows in Excel using the SUBTOTAL function.

The rules for using the SUBTOTAL function in Excel are the following:

  • The SUBTOTAL function is used to return the subtotal of the selected range or data set.
  • The function will automatically ignore cells that have been filtered out of the view. Furthermore, it automatically ignores existing subtotal formulas to avoid double counting. 
  • Additionally, the SUBTOTAL function can perform many calculations such as SUM, COUNT, MAX, MIN, AVERAGE, and others.
  • When function_num is between 1-11, the function will include manually hidden rows.
  • When function_num is between 101-111, the function will exclude manually hidden rows.
  • If the list or data set is filtered, the function will always ignore values in hidden rows regardless of function_num.
  • If the range is horizontal, the values in hidden columns are always included. 

Excel is an excellent tool to use since it has several built-in functions and tools. Furthermore, we can easily manipulate and organize the values in our data set. For instance, we can apply a filter that will allow us to hide or filter specific data in the data set.

Thus, we will focus on learning how to count filtered rows in Excel. And the easiest way to count the number of cells in a filtered data set is by using the SUBTOTAL function. 

Let’s take a sample scenario wherein we need to count filtered rows in Excel.

Suppose you have a data set containing the sales for different dates. And you want to count the number of sales made in the month of October. Hence, you first filtered your data set to only display the rows containing October dates. Then, you used the SUBTOTAL function to count the number of rows. 

Before we move on to a real example of counting filtered rows in Excel, let’s first learn the syntax of the SUBTOTAL function.

 

The Anatomy of the SUBTOTAL Function

The syntax or the way we write the SUBTOTAL function is as follows:

=SUBTOTAL(function_num, ref1,...)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • SUBTOTAL() refers to our SUBTOTAL function. And this function is used to return a subtotal in a list or database.
  • function_num is a required argument. So this refers to the number 1 to 11 that will specify the summary function for the subtotal.
  • ref1 is another required argument. And this refers to 1 to 254 ranges or references for which we want to get the subtotal.

Note: The SUBTOTAL function works best with vertical data. 

Great! Now we can dive into a real example of counting filtered rows in Excel using the SUBTOTAL function. 

 

A Real Example of Counting Filtered Rows in Excel

Let’s say we have a data set containing the different dates for each sale in certain months. And we only have two columns for the date and number of sales. So our initial data set would look like this:

Initial dataset

 

Moreover, our main goal is to count the number of sales made in the month of October. But, the data set also contains different sales dates from other months. Hence, we would need to first filter out the rows in our data set to only display the dates from the month of October. 

So we can easily go to the Data tab and apply a filter to our data set. Afterward, we can simply filter out the rows by only checking the box for October. Finally, we only have filtered rows in our data set.

Next, we need to count the filtered rows, which would give us the number of sales made in the month of October. And this is where we can utilize the SUBTOTAL function. 

So the SUBTOTAL function is used to run a given calculation on the selected range of cells while ignoring cells that should not be included. Since the SUBTOTAL function ignores cells that are filtered out of the data set, it is the perfect function to use in our filtered data set. 

Furthermore, the SUBTOTAL function can perform many different calculations determined by the function_num argument. So there are a total of 11 calculations with two options each. Additionally, these values are paired together, which is related to how the function deals with manually hidden rows. 

For example, when the function_num is between 1-11, the function will include rows that are manually hidden. When the function_num is between 101-111, the function will exclude rows that are manually hidden. 

To make it easier to understand and remember, here is a table to ensure you can correctly use the SUBTOTAL function.

Function/ Calculation Include hidden rows Exclude hidden rows
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV 7 107
STDEVP 8 108
SUM 9 109
VAR 10 110
VARP 11 111

Hence, we will use 103 which will use the COUNTA function to count all cells that are not empty. Moreover, it will exclude hidden rows from the count. Alternatively, we can also use 102, which uses the COUNT function to count only the cells containing numbers. And this will also exclude hidden rows.

After filtering out the rows to only display the dates from October, we can now use a SUBTOTAL formula with the value 103 to count the number of filtered rows in the data set. So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed to discuss the process of how to count filtered rows in Excel.

 

How to Count Filtered Rows in Excel

In this section, we will discuss the step-by-step process of how to count filtered rows in Excel using the SUBTOTAL function. Additionally, each step has detailed instructions and pictures. 

1. Firstly, we will filter our data set. Next, we need to select the entire data set and go to the Data tab. Then, we will click the Filter icon.

Apply a filter

 

2. Secondly, we will click the dropdown arrow at the right corner of the Date column. Next, we will uncheck all the boxes except for October. Lastly, we will click Apply to filter the rows accordingly.

Filter the rows

 

3. Thirdly, we will now count the filtered rows. Afterward, we can simply type in the formula “=SUBTOTAL(103, B2:B4)”. Lastly, we will press the Enter key to return the result.

Count Filtered Rows in Excel

 

4. However, we can also use the formula “=SUBTOTAL(102, B2:B4)”. Then, press the Enter key to return the result.

Count Filtered Rows in Excel

 

5. And tada! We have successfully counted the filtered rows in Excel.

Count Filtered Rows in Excel

 

And that’s pretty much it! We have successfully discussed how to count filtered rows in Excel using the SUBTOTAL function. Now you can apply this method to your work whenever you need to count filtered cells.

Are you interested in learning more about what Excel can do? You can now use the SUBTOTAL function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like