Most of us may experience the need to multiply two columns and then sum in Excel, but it will be difficult to compute hundreds or thousands of rows. There is also a strong function in Excel: SUMPRODUCT, which allows us to multiply two columns quickly and add them. Also, we can use this function based on one or two even more criteria. In this tutorial, I will show the process of multiplying two columns and then sum in Excel

## Overview of Excel SUMPRODUCT Function

One or more arrays can be sent as arguments to **SUMPRODUCT**, which multiplies the corresponding values of each array before returning the sum of the products.

**⇒ Syntax**

**SUMPRODUCT(array1,[array2],[array3],…)**

**⇒ Argument**

**array1: (Required)**The first array of numbers.**array2: (Optional)**The second array of numbers.**array3: (Optional)**The third array of numbers.

**⇒ Return Value**

Returns the sum of the products of the corresponding values from all the arrays.

## How to Multiply Two Columns and Then Sum in Excel: 3 Examples:

In this section, I will show you 3 quick and easy Examples to multiply two columns and then sum in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

The following figure shows the product name, its selling region, quantity sold, and unit price. Now if you want to determine the total price of all products sold over all regions, surely, you surely have to multiply the two columns e.g. quantity (**Column-D**) and unit price (**Column-E**). But without using the Excel function I mean if you like to find out the value manually, especially for the larger dataset, it will be difficult and exasperating. But the desired value even with criteria can be found quickly.

### 1. Multiply Two Columns and Sum Their Products Using SUMPRODUCT Function in Excel

When you have to find out the total sales amount of all products without criteria, you can simply use the **SUMPRODUCT** function. Multiply **column D **and **column E**. The cell range for quantity is **D5:D10** and **E5:E10** for unit price. Then enter the formula in a blank cell like the following:

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

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

### 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 a little like the following:

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

**🔎 Formula Breakdown:**

Here, **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

### 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:**

Here,** 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**”.

- Thus, you have got the sum product under multiple criteria in multiple columns.
- Also, for iterative search, you can create cell references for the criteria to calculate the sum product.
- Here, I have inserted two criteria in cells B14 and C14: one is the region name and the other is the product.
- After that, insert 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

Now if you want to get the total sales of two or more products of any region then you have to make multiple criteria in the same column. Here, I will show you an example where I have calculated the total sales of Monitor and Keyboard in all regions. Follow the steps below for this:

- First, insert the product names for which you want to calculate total sales in cells
**C14**and**C15**. - Then, insert 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)`

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

- For this, insert the region names in cells
**B14**and**B15**. - Then, insert the following formula into cell
**D13**:

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

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

That’s how you can easily multiply two columns and sum them in Excel. Now you have these ways, you’ll have no trouble multiplying two columns and sum. Thank you for reading this article and stay tuned for our next post. You can visit our website, ExcelDemy, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.

## 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**