To SUM the result of multiplying corresponding cells in Excel, you can use the SUMPRODUCT function.
The SUMPRODUCT function multiplies corresponding components in the given arrays and returns the sum of those products. Here's how to use it:
Using the SUMPRODUCT Function
The general syntax is:
=SUMPRODUCT(array1, array2, array3, ...)
Where:
array1,array2,array3, etc., are the ranges of cells you want to multiply and then sum. The arrays must have the same dimensions.
Example:
Let's say you have quantities in column C (C2:C5) and prices in column D (D2:D5). To calculate the total value, you would use the following formula:
=SUMPRODUCT(C2:C5,D2:D5)
Explanation:
This formula does the following:
- Multiplies C2 by D2, C3 by D3, C4 by D4, and C5 by D5.
- Sums the results of these multiplications.
Example Data:
| Quantity (Column C) | Price (Column D) |
|---|---|
| 2 | 10 |
| 3 | 15 |
| 4 | 5 |
| 1 | 20 |
In this case, =SUMPRODUCT(C2:C5,D2:D5) would calculate:
(2 10) + (3 15) + (4 5) + (1 20) = 20 + 45 + 20 + 20 = 105
Alternatives (Less Efficient):
While SUMPRODUCT is the most straightforward method, you could achieve the same result using a combination of multiplication and SUM, though it's generally less efficient:
- In a new column (e.g., Column E), multiply the quantity and price for each row (e.g., in E2,
=C2*D2, then copy down). - Use the
SUMfunction to add up the values in Column E (e.g.,=SUM(E2:E5)).
However, SUMPRODUCT performs this calculation in a single step, making it the preferred approach.
