Do you have a spreadsheet where you want to obtain the total of different products by different values? SUMPRODUCT is a very useful formula and can replace using multiple COUNTIFS or SUMIFS formulas.
In the spreadsheet below we would like to calculate how much was made in sales for the combinations in column H.
In order to calculate the total sales for Product 1, we can use the following formula in cell I2;
=SUMPRODUCT((C2:C10="Product 1")*(D2:D10)*(E2:E10))
This formula means for every Product 1, calculate the Quantity by the Price.
In this example it calculated $150 for row 2 and $110 for row 4. Then it added these results together;
Now we want to create a calculation for Product 5 and Texas (TX). We would use the below formula for cell I3.
=SUMPRODUCT((A2:A10="TX")*(C2:C10="Product 5")*(D2:D10)*(E2:E10))
Excel first pinpoints the value of TX in column A, then it looks for Product 5 in column C. Then it multiplies the corresponding Quantity in column D by the Price in column E.
For the last combination, we would simply change Product 5 in the double quotes to Product 2.
Comments