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