Table of Contents
When using SUMPRODUCT with SUBTOTAL in Excel, incorrect results can occur if the SUBTOTAL function excludes any cells that are included in the SUMPRODUCT calculation. This is because the SUBTOTAL function ignores any hidden cells, while the SUMPRODUCT function still includes them in the calculation, resulting in incorrect results.
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)))
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.