Md. Abdul Kader
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.
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 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”.
Read More: How to Multiply Multiple Cells in Excel (4 Methods)
Similar Readings
- How to Divide and Multiply in One Excel Formula (4 Ways)
- Do Matrix Multiplication in Excel (5 Examples)
- How to Make Multiplication Table in Excel (4 Methods)
- Multiply One Cell by Multiple Cells in Excel (4 Ways)
- How to Multiply Rows in Excel (4 Easiest Ways)
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 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)
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)
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.
