You can use the following formula to combine the **SUBTOTAL** and **COUNTIF** functions in Excel:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Guard"))

This particular formula allows you to count the number of cells in the range B2:B11 equal to “Guard” even after that range of cells has been filtered in some way.

The following example shows how to use this formula in practice.

**Example: How to Use SUBTOTAL with COUNTIF in Excel**

Suppose we have the following dataset that contains information about various basketball players:

Next, let’s filter the data to only show the rows where the players are in the **West** conference.

To do so, highlight the cell range **A1:C11**. Then click the **Data **tab along the top ribbon and click the **Filter** button.

Then click the dropdown arrow next to **Conference **and make sure that only the box next to **West** is checked, then click **OK**:

The data will automatically be filtered to only show the rows where the Conference column is equal to **West**:

If we attempt to use the **COUNTIF()** function to count the number of rows where Position is equal to “Guard”, it will actually return the count of the rows equal to “Guard” in the original dataset:

Instead, we need to use the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Guard"))

The following screenshot shows how to use this formula in practice:

This function only counts the number of visible rows where Position is equal to “Guard”, which happens to be **4** rows.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

How to Delete Filtered Rows in Excel

How to Count Filtered Rows in Excel

How to Sum Filtered Rows in Excel