Most of us may experience the multiplication of two columns in Excel and then add them up, 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 of multiplication of two columns and then sum them up in excel.
Multiplication of Two Columns and Sum in Excel
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-C) and unit price (Column-D). 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. Before going to the main calculation, let’s be introduced to the main function.
SUMPRODUCT=(array1, array2, array3…) or (cell range1, cell range 2, cell range 3…)
According to the above syntax, if you have the two ranges A2 to A7 and B2 to B7. SUMPRODUCT multiplies cell A2 with B2, A3 with B3, and so on. Afterward, it sums up all single results. Also, this multiplication system can be used by employing criteria. For example, one criteria may be the price of monitors all over the regions. And two criteria may be the price of monitors over the Middle East region. In this way, you can add criteria.
1. Multiplication of Two Columns and Sum without any Criteria
When you have to find out the total sales amount of all products without criteria, you can simply use the SUMPRODUCT function. Multiply column C and column D. The cell range for quantity is C2:C7 and D2:D7 for unit price. Then enter the formula in a blank cell like the following:
2. Multiplication of Two Columns and Sum with One Criteria
If you want to get the price of monitors over all regions (one criteria) in the following figure, you can use the SUMPRODUCT function by expanding a little like the following:
where C2:C7, D2:D7, and A2:A7 are cell range of quantity, unit price, and product name respectively.
3. Multiplication of Two Columns and Sum with Two or More Criteria
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:
Here, C2:C7, B2:B7, D2:D7, and A2:A7 are cell ranges of quantity, region, unit price, and product name respectively.
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. If you have any query or opinion, please share in the comments section below.