Related Function:

The SUMPRODUCT function multiplies corresponding values in two or more arrays, and returns the sum of those products.

Syntax

=SUMPRODUCT(array1,[array2],[array3] … )

Note: Beginning with Excel 2007, you can enter up to 255 number arguments to the function. Excel 2003 would only accept up to 30 number arguments.

Arguments

Argument Description
array1 The first array argument whose components you want to multiply and then add
[array2],
[array3], …
Optional. Array arguments 2 to 255 whose components you want to multiply and then add

Note: All of the supplied arrays must be of equal length and non-numeric values in the supplied arrays are treated as the value zero

Examples

  A B C D E F G H
1 Array 1 Array 2        
2 21 29 16 29        
3 37 45 48 47        
4 40 37 34 46        
5      
6 Formula Result Notes
7 =SUMPRODUCT(A2:B6) 209 Simply sums all the values in Array 1
8 =SUMPRODUCT(A2:B6,C2:D6) 8,130 Multiplies all the components of the two arrays and then adds the products, i.e. 21*16 + 29*29 + 27*48 + 45*47 + 40*34 +37*46

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the supplied arrays have different lengths, i.e. contain different numbers of values