Tip Printed from ExcelTip.com

---

Simplifying complicated multiplication calculations using the SUMPRODUCT function


Problem:

In order to sum up the multiplications of the first two values on each of columns A-Z, we would have to use a long formula such as this:
=Sum((A1*A2)+(B1*B2)+(C1*C2) ... (Z1*Z2)

Example:
A B C D
1 2 4 10
3 5 7 8

Solution 1:

Performing a short simple calculation using the SUMPRODUCT function as follows:

Formula: = SUMPRODUCT((A1:Z1)*(A2:Z2))
Result: 121

Solution 2:

Use an Array Formula:
(To perform an Array Formula: Insert the formula, press F2 and then press Ctrl+Shift+Enter)

Formula: {=SUM((B8:E8)*(B9:E9))}
Result: 121