How to Multiply Two Columns and then Sum in Excel

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.

Practice Spreadsheet

Click here to download the practice spreadsheet

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.

multiply-two-columns-and-sum_dataset

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:

=SUMPRODUCT(C2:C7,D2:D7)

Multiply two columns and sum without any criteria

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:

=SUMPRODUCT(C2:C7,D2:D7*(A2:A7="Monitor"))

where C2:C7, D2:D7, and A2:A7 are cell range of quantity, unit price, and product name respectively.

Multiplication of Two Columns and Sum with One Criteria

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:

=SUMPRODUCT(C2:C7*(B2:B7="Middle East"),D2:D7*(A2:A7="Monitor"))

Here, C2:C7, B2:B7, D2:D7, and A2:A7 are cell ranges of quantity, region, unit price, and product name respectively.

multiply two columns and sum_two criteria

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. If you have any query or opinion, please share in the comments section below.

Further Readings

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