**Example 1 – Multiply Two Columns and Sum Their Products Using SUMPRODUCT Function in Excel**

Multiply **column D **and **column E**. The cell range for quantity is **D5:D10** and **E5:E10** for unit price.

- Enter the following formula in a blank cell.

`=SUMPRODUCT(D5:D10,E5:E10)`

**Read More: **How to Multiply Multiple Cells in Excel

**Example 2 – Multiply Two Columns with One Criterion and Then Sum Them**

If you want to get the price of monitors over all regions (one criterion) in the following figure, you can use the **SUMPRODUCT **function by expanding like the following:

`=SUMPRODUCT(D5:D10,E5:E10*(B5:B10="Monitor"))`

** Formula Breakdown:**

**D5:D10**,** E5:E10**, and **B5:B10** are cell ranges of quantity, unit price, and product name respectively.

- Array1 =
**D5:D10 :**This is the 1st array of the multiplication. - Array2 =
**E5:E10*(B5:B10=”Monitor”) :**This means to include only those cells of range**E5:E10**for which**B5:B10**is equal to cell value “**Monitor**”.

**Read More: **How to Create a Multiplication Formula in Excel

**Example 3 – Multiplication and Summing of Two Columns with Multiple Criteria**

#### 3.1 In Multiple Columns

If you want to determine the price of monitors in the middle east region(two criteria), you can find the value using the **SUMPRODUCT** function.

- Enter the following formula into cell
**D12**to calculate the total amount of sales of**monitors**in the**Middle**East region.

`=SUMPRODUCT(D5:D10*(C5:C10="Middle East"),E5:E10*(B5:B10="Monitor"))`

** Formula Breakdown:**

**D5:D10**, **C5:C10**, **E5:E10**, and **B5:B10** are cell ranges of quantity, region, unit price, and product name respectively.

**Array1****=****D5:D10*(C5:C10=”Middle East” :**This is the**1st array**of the multiplication and it includes the only cells of range**D5:D10**which matches with value “**Middle East”**.**Array2****=****E5:E10*(B5:B10=”Monitor”) :**This means to include only those cells of range**E5:E10**for which**B5:B10**is equal to cell value “**Monitor**”.

- You have the sum product under multiple criteria in multiple columns.
- For iterative search, you can create cell references for the criteria to calculate the sum product.
- I have inserted two criteria in cells B14 and C14: one is the region name and the other is the product.
- Enter the following formula into cell D13 to calculate the total sales of the specified product at the specified region.

`=SUMPRODUCT(((B5:B10=B14)+(C5:C10=C14)),D5:D10,E5:E10)`

#### 3.2 In the Same Column

If you want to get the total sales of two or more products of any region, you have to make multiple criteria in the same column. Here, I have calculated the total sales of Monitor and Keyboard in all regions.

- Enter the product names for which you want to calculate total sales in cells
**C14**and**C15**. - Enter the following formula to calculate the total sales of the selected products in all regions:

`=SUMPRODUCT(((B5:B10=C14)+(B5:B10=C15)),D5:D10,E5:E10)`

You can use a similar formula to calculate the total sales of multiple regions without specifying any products.

- Enter the region names in cells
**B14**and**B15**. - Enter the following formula into cell
**D13**:

`=SUMPRODUCT(((C5:C10=B14)+(C5:C10=B15)),D5:D10,E5:E10)`

**Download Practice Workbook**

## Related Articles

- How to Multiply from Different Sheets in Excel

- How to Multiply by Percentage in Excel
- If Cell Contains Value Then Multiply Using Excel Formula
- How to Divide and Multiply in One Excel Formula
- Do Matrix Multiplication in Excel
- How to Make Multiplication Table in Excel
- How to Multiply Rows in Excel

**<< Go Back to Multiply in Excel | Calculate in Excel | Learn Excel**