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
