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

=SUMPRODUCT(C2:C11,SUBTOTAL(9,OFFSET(D2:D11,ROW(D2:D11)-MIN(ROW(D2:D11)),0,1)))

This particular formula allows you to sum the product of the values in the range **C2:C11** and the range **D2:D11** 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 SUMPRODUCT in Excel**

Suppose we have the following dataset that contains information about the sales of various products at two different grocery stores:

Next, let’s filter the data to only show the rows where the value in the Store column is **B**.

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

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

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

If we attempt to use the **SUMRPODUCT()** function to sum the product of the values in the Sales and Price columns, it will actually return the sum of the product of these two columns in the original dataset:

Instead, we need to use the following formula:

=SUMPRODUCT(C2:C11,SUBTOTAL(9,OFFSET(D2:D11,ROW(D2:D11)-MIN(ROW(D2:D11)),0,1)))

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

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

We can confirm this is correct by manually calculating the sum of the product of the values between the Sales and Price columns:

Sum of Product of Values between Sales and Price: (3*2) + (12*2.5) + (5*3.5) + (8*3) = **77.5**.

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