Difference between revisions of "Documentation/How Tos/Calc: SUMPRODUCT function"
From Apache OpenOffice Wiki
< Documentation | How Tos
| Line 10: | Line 10: | ||
: You can use <tt>'''SUMPRODUCT'''</tt> to calculate the scalar product of two vectors. | : You can use <tt>'''SUMPRODUCT'''</tt> to calculate the scalar product of two vectors. | ||
| − | : <tt>'''SUMPRODUCT'''</tt> can also be used to sum cells where a specified condition is true - see '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''. | + | :<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> can also be used to sum cells where a specified condition is true - see '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''. | ||
=== Example: === | === Example: === | ||
| + | [[Image:Calc_sumproduct_example.png|Example use of SUMPRODUCT function|right]] | ||
<tt>'''SUMPRODUCT(A1:B2; C1:D2)'''</tt> | <tt>'''SUMPRODUCT(A1:B2; C1:D2)'''</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>: | :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> |
| + | :returns the sum of cells in <tt>'''C1:C6'''</tt> whose corresponding entries in the A column are <tt>'''red'''</tt> and in the B column are <tt>'''big'''</tt>. | ||
=== See also: === | === See also: === | ||
| Line 24: | Line 29: | ||
'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | ||
| + | |||
| + | [[Documentation/How_Tos/Using Arrays|'''How To Use Arrays in Calc''']] | ||
'''[[Documentation/How_Tos/Calc: Array functions|Array functions]]''' | '''[[Documentation/How_Tos/Calc: Array functions|Array functions]]''' | ||
Revision as of 06:35, 15 March 2008
SUMPRODUCT
Multiplies corresponding elements in the given ranges, and returns the sum of those products.
Syntax:
SUMPRODUCT(range1; range2; ... range30)
- range1 to range30 are up to 30 ranges whose corresponding elements are to be multiplied.
- You can use SUMPRODUCT to calculate the scalar product of two vectors.
- SUMPRODUCT evaluates in array mode, but does not need to be entered as an array formula.
- SUMPRODUCT can also be used to sum cells where a specified condition is true - see Conditional Counting and Summation.
Example:
SUMPRODUCT(A1:B2; C1:D2)
- returns A1*C1 + B1*D1 + A2*C2 + B2*D2:
=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.
See also:
SUM function, SUMIF function, SUMSQ function
