How to Multiply Two Columns and Then Sum in Excel (3 Examples)

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)

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”.

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:

Multiplication of Two Columns and Then Sum.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!