Table of Contents
SUBTOTAL and SUMPRODUCT are two powerful functions in Microsoft Excel that can be used together to perform advanced calculations on a range of data. SUBTOTAL allows you to apply a function (such as SUM or AVERAGE) to a subset of data within a larger range, while SUMPRODUCT multiplies corresponding values in specified arrays and returns the sum of those products. By combining these two functions, you can easily calculate subtotals for specific categories within a dataset, while also taking into account any filters or hidden rows. This can be useful for analyzing large datasets and creating customized reports. To use SUBTOTAL with SUMPRODUCT, simply include the SUBTOTAL function as one of the arrays in the SUMPRODUCT formula. This will ensure that the subtotal calculation is only applied to the visible cells, making your analysis more accurate and efficient.
Use SUBTOTAL with SUMPRODUCT in Excel
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.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I use SUBTOTAL with SUMPRODUCT in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumproduct-in-excel/
stats writer. "How can I use SUBTOTAL with SUMPRODUCT in Excel?." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumproduct-in-excel/.
stats writer. "How can I use SUBTOTAL with SUMPRODUCT in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumproduct-in-excel/.
stats writer (2024) 'How can I use SUBTOTAL with SUMPRODUCT in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-subtotal-with-sumproduct-in-excel/.
[1] stats writer, "How can I use SUBTOTAL with SUMPRODUCT in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use SUBTOTAL with SUMPRODUCT in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.