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 fast 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 to multiply 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.


Download Practice Workbook

You can download the practice workbook from here:


3 Examples: Multiply Two Columns and Then Sum in Excel

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 have to multiplicate 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.

Sample Dataset to Multiply Two Columns and Then Sum in Excel


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)

Multiplication of Two Columns and Sum without Any Criteria

Read More: How to Multiply in Excel (8 Simple Methods)


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

Multiplication of Two Columns and Sum with One Criteria

Read More: How to Multiply Multiple Cells in Excel (4 Methods)


Similar Readings


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

Multiplication of Two Columns and Sum with Multiple Criteria

  • 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 sumproduct.
  • 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)

How to Multiply Two Columns and Then Sum in Excel

Read More: What is the Formula for Multiplication in Excel for Multiple Cells? (3 Ways)


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)

Multiple Criteria In the Same Column

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)

Read More: How to Multiply a Column in Excel by a Constant (4 Easy Ways)


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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo