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

From Apache OpenOffice Wiki
Jump to: navigation, search
(Example:)
(17 intermediate revisions by 5 users not shown)
Line 1: Line 1:
__NOTOC__
+
{{Documentation/MasterTOC
 +
|bookid=1234'''
 +
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
 +
|ShowParttitle=block
 +
|parttitle=[[Documentation/How_Tos/Calc:_Array_functions|<div  style="font-size: 140%;">Arrays Functions]]
 +
|ShowNextPage=block
 +
|NextPage= Documentation/How_Tos/Calc:_SUMX2MY2_function
 +
|ShowPrevPage=block
 +
|PrevPage= Documentation/How_Tos/Calc:_MUNIT_function
 +
|ShowPrevPart=block
 +
|PrevPart= Documentation/Reference/Calc_functions
 +
|ShowNextPart=block
 +
|NextPart= Documentation/How_Tos/Calc:_Complex_Number_functions
 +
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>
 +
 
 +
* [[Documentation/How_Tos/Calc:_FREQUENCY_function|<div style="font-size: 120%;">Frequency]]
 +
* [[Documentation/How_Tos/Calc:_GROWTH_function|<div style="font-size: 120%;">Growth]]
 +
* [[Documentation/How_Tos/Calc:_LINEST_function|<div style="font-size: 120%;">Linest]]
 +
* [[Documentation/How_Tos/Calc:_LOGEST_function|<div style="font-size: 120%;">Longest]]
 +
* [[Documentation/How_Tos/Calc:_MDETERM_function|<div style="font-size: 120%;">Mdeterm]]
 +
* [[Documentation/How_Tos/Calc:_MINVERSE_function|<div style="font-size: 120%;">Minverse]]
 +
* [[Documentation/How_Tos/Calc:_MMULT_function|<div style="font-size: 120%;">Mmult]]
 +
* [[Documentation/How_Tos/Calc:_MUNIT_function|<div style="font-size: 120%;">Munit]]
 +
* [[Documentation/How_Tos/Calc:_SUMPRODUCT_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Sumproduct</div>]]
 +
* [[Documentation/How_Tos/Calc:_SUMX2MY2_function|<div style="font-size: 120%;">Sumx2my2]]
 +
* [[Documentation/How_Tos/Calc:_SUMX2PY2_function|<div style="font-size: 120%;">Sumx2py2]]
 +
* [[Documentation/How_Tos/Calc:_SUMXMY2_function|<div style="font-size: 120%;">Sumxmy2]]
 +
* [[Documentation/How_Tos/Calc:_TRANSPOSE_function|<div style="font-size: 120%;">Transpose]]
 +
* [[Documentation/How_Tos/Calc:_TREND_function|<div style="font-size: 120%;">Trend]]
 +
}}
 +
__TOC__
  
 
== SUMPRODUCT  ==
 
== SUMPRODUCT  ==
Line 15: Line 45:
 
:<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 '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' and the example below.
+
: <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 29: Line 59:
  
 
<tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</tt>
 
<tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)'''</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>.
+
: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>. This is not portable to Excel, as Excel ignores logical values.
 
+
=== See also: ===
+
'''[[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]]'''
+
<tt>'''SUMPRODUCT((A1:A6="red") * (B1:B6="big") * C1:C6)'''</tt>
 +
: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/How_Tos/Using Arrays|'''How To Use Arrays in Calc''']]
+
=== 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.
  
'''[[Documentation/How_Tos/Calc: Array functions|Array functions]]'''
+
{{SeeAlso|EN|
 +
* [[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|How To Use Arrays in Calc]]
 +
* [[Documentation/How_Tos/Calc: Array functions|Array functions]]
 +
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]]
 +
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}}
 +
[[Category: Documentation/Reference/Calc/Array functions]]

Revision as of 12:47, 16 July 2018


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 Calc sumproduct equation.png 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.

Issues:

  • In Excel SUMPRODUCT ignores logical values (that is, it treats them as 0). In Calc TRUE is 1 and FALSE is 0.



See Also
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_SUMPRODUCT_function&oldid=243258"
Views
Personal tools
Navigation
Tools