Difference between revisions of "Documentation/How Tos/Calc: SUMPRODUCT function"

Revision as of 07:27, 15 March 2008

Returns the sum of the products of corresponding array elements.

SUMPRODUCT(array1; array2; ... array30)

array1 to array30 are up to 30 arrays or ranges of the same size whose corresponding elements are to be multiplied.

SUMPRODUCT returns

for the i elements in the arrays. It evaluates in array mode, but does not need to be entered as an array formula.

You can use SUMPRODUCT to calculate the scalar product of two vectors.

SUMPRODUCT can also be used to sum cells where a specified condition is true - see Conditional Counting and Summation and the example below.

SUMPRODUCT(A1:B2; F1:G2)

returns A1*F1 + B1*G1 + A2*F2 + B2*G2.

SUMPRODUCT(A1:A6=”red”; B1:B6=”big”; C1:C6)

returns the sum of cells in C1:C6 whose corresponding entries in the A column are red and in the B column are big.

@@ Line 6: / Line 6: @@
 === Syntax: ===
 <tt>'''SUMPRODUCT(array1; array2; ... array30)'''</tt>
-: <tt>'''array1'''</tt> to <tt>'''array30'''</tt> are up to 30 arrays or ranges whose corresponding elements are to be multiplied.
+: <tt>'''array1'''</tt> to <tt>'''array30'''</tt> are up to 30 arrays or ranges of the same size whose corresponding elements are to be multiplied.
-:<tt>'''SUMPRODUCT'''</tt> evaluates in array mode, but does not need to be entered as an [[Documentation/How_Tos/Using Arrays|array formula]].
+:<tt>'''SUMPRODUCT'''</tt> returns [[Image:Calc_sumproduct_equation.png]] for the <i>i</i> elements in the arrays. It evaluates in array mode, but does not need to be entered as an [[Documentation/How_Tos/Using Arrays|array formula]].
 : You can use <tt>'''SUMPRODUCT'''</tt> to calculate the scalar product of two vectors.
@@ Line 15: / Line 15: @@
 === Example: ===
-[[Image:Calc_sumproduct_example.png|Example use of SUMPRODUCT function|right]]
+<tt>'''SUMPRODUCT(A1:B2; F1:G2)'''</tt>
-<tt>'''SUMPRODUCT(A1:B2; C1:D2)'''</tt>
+:returns <tt>'''A1'''</tt>*<tt>'''F1'''</tt> + <tt>'''B1'''</tt>*<tt>'''G1'''</tt> + <tt>'''A2'''</tt>*<tt>'''F2'''</tt> + <tt>'''B2'''</tt>*<tt>'''G2'''</tt>.
-:returns <tt>'''A1'''</tt>*<tt>'''C1'''</tt> + <tt>'''B1'''</tt>*<tt>'''D1'''</tt> + <tt>'''A2'''</tt>*<tt>'''C2'''</tt> + <tt>'''B2'''</tt>*<tt>'''D2'''</tt>:
-<br style="clear:both;" />
 <tt>'''<nowiki>SUMPRODUCT(A1:A6=”red”; B1:B6=”big”; C1:C6)</nowiki>'''</tt>