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

=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C11)-ROW(C2),,1)),--(B2:B11="Guard"))

This particular formula allows you to sum the values in the range C2:C11 where the corresponding value in the range B2:B11 is 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 SUMIF 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 **SUMIF()** function to sum the values in the Points column where the value in the Position column is equal to “Guard”, it will actually return the sum of points for the rows equal to “Guard” in the original dataset:

Instead, we need to use the following formula:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C11)-ROW(C2),,1)),--(B2:B11="Guard"))

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

This formula returns the correct sum of **85**.

We can confirm this is correct by manually calculating the sum of the points values for the rows where the Position column is equal to “Guard”:

Sum of Points for Guards: 12 + 28 + 30 + 15 = **85**.

**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

Very useful. Solved my problem.

Thank a lot.

Thanks so much for this… but I do have one question… in the formual what does the 109 stand for?

YOU ARE AMAZING!!!!!!

Thanks!!!! i’ve been looking for this solution for about 5 years.

I don’t undersdant how it does what it does but it works!!

Keep it Up!

Thanks Zach. I don’t know how I would have came up with this formula on my own.