SUMPRODUCT function

Category: Mathematical functions

What does this function do?

Multiplies two or more arrays and returns the sum of the products.

Syntax:

SUMPRODUCT(array1,array2,array3, ...)

• Array1, array2, array3..: are 2 to 255 arrays whose components you want to multiply and then add. The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. SUMPRODUCT treats array entries that are not numeric as if they were zeros. Minimum array1 required. 

Examples:

Suppose A1:A3 = {2,3,4} and B1:B3 = {9,8,7}
= SUMPRODUCT (A1:A3,B1:B3) = {2 * 9 + 3 * 8 + 4 * 7} = 70
As you can see, what this function is doing is multiplying each value of the first array (A1:A3) by its corresponding value in the second matrix (B1:B3) and then sum the products.