You can use the SUMPRODUCT function in Excel to multiply the values between two columns and then calculate the sum.
For example, you can use the following syntax to multiply the values between the ranges B2:B12 and C2:C12 and then calculate the sum:
=SUMPRODUCT(B2:B12, C2:C12)
The following example shows how to use this formula in practice.
Example: Use SUMPRODUCT to Multiply Two Columns and then Sum
Suppose we have the following dataset in Excel that contains information about the price of various products and total number of units sold by some company:
Suppose we would like to calculate the total revenue for this company by multiplying the values between the Price and Units columns and then calculating the sum.
We can type the following formula into cell B15 to do so:
=SUMPRODUCT(B2:B12, C2:C12)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 761.
This represents the total revenue for the company.
We can confirm this is correct by manually multiplying the values between the Price and Units columns and then calculating the sum:
Total Revenue: (10*4) + (12*3) + (15*9) + (18*5) + (20*5) + (22*4) + (10*10) + (8*12) + (5*3) + (5*5) + (9*4) =761.
This matches the value calculated by our SUMPRODUCT formula.
Note: You can find the complete documentation for the SUMPRODUCT function here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use SUMPRODUCT Across Multiple Sheets
Excel: Using SUMPRODUCT Only with Values Greater Than Zero
Excel: How to Use SUBTOTAL with SUMPRODUCT
