Difference between revisions of "Documentation/How Tos/Calc: SUMPRODUCT function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m (→Issues:) |
OOoWikiBot (Talk | contribs) m (Robot: Automated text replacement %s) |
||
| Line 15: | Line 15: | ||
:<i>Advanced topic:</i> | :<i>Advanced topic:</i> | ||
| − | :<tt>'''SUMPRODUCT'''</tt> evaluates each parameter <tt>'''array1'''</tt> to <tt>'''array30'''</tt> as an [[Documentation/How_Tos/Using Arrays|array formula]], but does not need to be entered as an array formula. In other words it can be entered with the '''Enter''' key, rather than '''Cntrl-Shift-Enter'''. See the example below. | + | :<tt>'''SUMPRODUCT'''</tt> evaluates each parameter <tt>'''array1'''</tt> to <tt>'''array30'''</tt> as an * [[Documentation/How_Tos/Using Arrays|array formula]], but does not need to be entered as an array formula. In other words it can be entered with the '''Enter''' key, rather than '''Cntrl-Shift-Enter'''. See the example below. |
| − | : <tt>'''SUMPRODUCT'''</tt> can also be used to sum cells where a specified condition is true - see | + | : <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]]''' and the example below. |
=== Example: === | === Example: === | ||
| Line 26: | Line 26: | ||
<tt>'''SUMPRODUCT(ABS(A1:A6))'''</tt> | <tt>'''SUMPRODUCT(ABS(A1:A6))'''</tt> | ||
| − | : when entered 'normally' (not as an [[Documentation/How_Tos/Using Arrays|array formula]]), returns the sum of the absolute values in cells <tt>'''A1:A6'''</tt>. <tt>'''SUMPRODUCT'''</tt> forces <tt>'''ABS(A1:A6)'''</tt> to be evaluated as an [[Documentation/How_Tos/Using Arrays|array formula]]. | + | : when entered 'normally' (not as an * [[Documentation/How_Tos/Using Arrays|array formula]]), returns the sum of the absolute values in cells <tt>'''A1:A6'''</tt>. <tt>'''SUMPRODUCT'''</tt> forces <tt>'''ABS(A1:A6)'''</tt> to be evaluated as an * [[Documentation/How_Tos/Using Arrays|array formula]]. |
<tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt> | <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt> | ||
| Line 34: | Line 34: | ||
:a version of <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt> which is portable to Excel, as the logical values are converted to numbers during the multiplication. | :a version of <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt> which is portable to Excel, as the logical values are converted to numbers during the multiplication. | ||
| − | + | {{Documentation/SeeAlso| | |
| − | + | * [[Documentation/How_Tos/Calc: SUM function|SUM]] | |
| − | + | * [[Documentation/How_Tos/Calc: SUMIF function|SUMIF]] | |
| − | + | * [[Documentation/How_Tos/Calc: SUMSQ function|SUMSQ]] | |
| − | + | * [[Documentation/How_Tos/Calc: SUMX2MY2 function|SUMX2MY2]] | |
| − | + | * [[Documentation/How_Tos/Calc: SUMX2PY2 function|SUMX2PY2]] | |
| − | + | * [[Documentation/How_Tos/Calc: SUMXMY2 function|SUMXMY2]]''' | |
| − | + | * [[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | |
| − | [[Documentation/How_Tos/Using Arrays| | + | * [[Documentation/How_Tos/Using Arrays|How To Use Arrays in Calc]] |
| − | + | * [[Documentation/How_Tos/Calc: Array functions|Array functions]]''' | |
| − | [[Documentation/How_Tos/Calc: Functions listed alphabetically| | + | * [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] |
| − | [[Documentation/How_Tos/Calc: Functions listed by category| | + | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} |
=== Issues: === | === Issues: === | ||
| − | *In Excel SUMPRODUCT ignores [[Documentation/How_Tos/Calc: Logical functions#Logical values overview|logical values]] (that is, it treats them as 0). In Calc <tt>'''TRUE'''</tt> is 1 and <tt>'''FALSE'''</tt> is 0. | + | *In Excel SUMPRODUCT ignores * [[Documentation/How_Tos/Calc: Logical functions#Logical values overview|logical values]] (that is, it treats them as 0). In Calc <tt>'''TRUE'''</tt> is 1 and <tt>'''FALSE'''</tt> is 0. |
Revision as of 14:20, 25 February 2009
SUMPRODUCT
Returns the sum of the products of corresponding array elements.
Syntax:
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.
- You can use SUMPRODUCT to calculate the scalar product of two vectors.
- Advanced topic:
- SUMPRODUCT evaluates each parameter array1 to array30 as an * array formula, but does not need to be entered as an array formula. In other words it can be entered with the Enter key, rather than Cntrl-Shift-Enter. See the example below.
- SUMPRODUCT can also be used to sum cells where a specified condition is true - see * Conditional Counting and Summation and the example below.
Example:
SUMPRODUCT(A1:B2; F1:G2)
- returns A1*F1 + B1*G1 + A2*F2 + B2*G2.
Advanced topic:
SUMPRODUCT(ABS(A1:A6))
- when entered 'normally' (not as an * array formula), returns the sum of the absolute values in cells A1:A6. SUMPRODUCT forces ABS(A1:A6) to be evaluated as an * array formula.
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. This is not portable to Excel, as Excel ignores logical values.
SUMPRODUCT((A1:A6="red") * (B1:B6="big") * C1:C6)
- a version of SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6) which is portable to Excel, as the logical values are converted to numbers during the multiplication.
Template:Documentation/SeeAlso
Issues:
- In Excel SUMPRODUCT ignores * logical values (that is, it treats them as 0). In Calc TRUE is 1 and FALSE is 0.
