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

##### Table of Contents

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:

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:

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.

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.

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.

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

5. And tada! We have successfully counted the 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.