https://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&action=history&feed=atomSUMIF and SUMPRODUCT Functions in Calc - Revision history2024-03-29T08:47:34ZRevision history for this page on the wikiMediaWiki 1.10.1https://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=13240&oldid=prevJgd: /* SUMIF Function */ SUMIF can sometimes take only 2 arguments2008-01-20T19:06:14Z<p><span class="autocomment">SUMIF Function -</span> SUMIF can sometimes take only 2 arguments</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 19:06, 20 January 2008</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 18:</strong></td>
<td colspan="2" align="left"><strong>Line 18:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">This function is a mathematical function, not an array formula. It allows you to sum a cell range which constitutes a matrix and meets some criteria. These criteria must be '''a number or a string'''. The syntax is:</td><td> </td><td style="background: #eee; font-size: smaller;">This function is a mathematical function, not an array formula. It allows you to sum a cell range which constitutes a matrix and meets some criteria. These criteria must be '''a number or a string'''. The syntax is:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(range _to_be_tested;criteria;sum_range)'''<br></td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(range _to_be_tested;criteria;sum_range)'''<br></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">'''Note''': if the range to be tested and the sum range are the same, it's enough to enter the formula:</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">::'''=SUMIF(range _to_be_tested;criteria)'''<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values.</td><td> </td><td style="background: #eee; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*To sum the odd numbers in this range, you have to use an auxiliary column, for example B, to show the parity of the numbers in the range A1:A10.<br></td><td> </td><td style="background: #eee; font-size: smaller;">*To sum the odd numbers in this range, you have to use an auxiliary column, for example B, to show the parity of the numbers in the range A1:A10.<br></td></tr>
<tr><td colspan="2" align="left"><strong>Line 29:</strong></td>
<td colspan="2" align="left"><strong>Line 31:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::and press {{key|Return}}.</td><td> </td><td style="background: #eee; font-size: smaller;">::and press {{key|Return}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*To sum the numbers in the range A1:A20 which are greater than the average of this range, you don't need an auxiliary column, you can enter the following formula in an empty cell:</td><td> </td><td style="background: #eee; font-size: smaller;">*To sum the numbers in the range A1:A20 which are greater than the average of this range, you don't need an auxiliary column, you can enter the following formula in an empty cell:</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;"></td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">::'''=SUMIF(A1:A20;">"&AVERAGE(A1:A20))'''</td></tr>
<tr><td colspan="2"> </td><td>+</td><td style="background: #cfc; font-size: smaller;">or</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(A1:A20;">"&AVERAGE(A1:A20);A1:A20)'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(A1:A20;">"&AVERAGE(A1:A20);A1:A20)'''</td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=13233&oldid=prevJgd: /* SUMPRODUCT Function */2008-01-20T08:46:13Z<p><span class="autocomment">SUMPRODUCT Function</span></p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 08:46, 20 January 2008</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 49:</strong></td>
<td colspan="2" align="left"><strong>Line 49:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">We suppose again that the range A1:A20 contains numeric values.<br></td><td> </td><td style="background: #eee; font-size: smaller;">We suppose again that the range A1:A20 contains numeric values.<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">The '''SUMPRODUCT''' function is an array function As it returns a single number, it can be entered by pressing only the {{key|Return}} key. It can be used:<br></td><td>+</td><td style="background: #cfc; font-size: smaller;">The '''SUMPRODUCT''' function is an array function<ins style="color: red; font-weight: bold; text-decoration: none;">. </ins>As it returns a single number, it can be entered by pressing only the {{key|Return}} key. It can be used:<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">1) To calculate the scalar product of two vectors.</td><td> </td><td style="background: #eee; font-size: smaller;">1) To calculate the scalar product of two vectors.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=13232&oldid=prevJgd: remove fixme (Lorinda did "polish")2008-01-20T08:44:52Z<p>remove fixme (Lorinda did "polish")</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 08:44, 20 January 2008</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 1:</strong></td>
<td colspan="2" align="left"><strong>Line 1:</strong></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">{{fixme}}</td><td colspan="2"> </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">The SUMIF function belongs to the mathematical functions category and the SUMPRODUCT function to the array functions one. They allow you to work with cell ranges which meet some criteria. Before describing them, it's good to explain the difference between a ''function'' (or formula) and an ''array function'' (or array formula).</td><td> </td><td style="background: #eee; font-size: smaller;">The SUMIF function belongs to the mathematical functions category and the SUMPRODUCT function to the array functions one. They allow you to work with cell ranges which meet some criteria. Before describing them, it's good to explain the difference between a ''function'' (or formula) and an ''array function'' (or array formula).</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">==Formulas and Array Formulas==</td><td> </td><td style="background: #eee; font-size: smaller;">==Formulas and Array Formulas==</td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=12734&oldid=prevValterb: botlangbar -> IT2007-11-19T08:08:58Z<p>botlangbar -> IT</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 08:08, 19 November 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 107:</strong></td>
<td colspan="2" align="left"><strong>Line 107:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*Go to the {{menu|Data}} menu and choose {{menu|Define Range…}}.</td><td> </td><td style="background: #eee; font-size: smaller;">*Go to the {{menu|Data}} menu and choose {{menu|Define Range…}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*In the {{window|Define Range Database}} window which appears, enter a {{section|Name}}, for example "color" (without quotes).</td><td> </td><td style="background: #eee; font-size: smaller;">*In the {{window|Define Range Database}} window which appears, enter a {{section|Name}}, for example "color" (without quotes).</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">*<del style="color: red; font-weight: bold; text-decoration: none;">Clck </del>on {{button|Add}} then on {{button|OK}}.</td><td>+</td><td style="background: #cfc; font-size: smaller;">*<ins style="color: red; font-weight: bold; text-decoration: none;">Click </ins>on {{button|Add}} then on {{button|OK}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*Do the same with the range B1:B10 which you can call "size"</td><td> </td><td style="background: #eee; font-size: smaller;">*Do the same with the range B1:B10 which you can call "size"</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*You can now use the formula:</td><td> </td><td style="background: #eee; font-size: smaller;">*You can now use the formula:</td></tr>
<tr><td colspan="2" align="left"><strong>Line 116:</strong></td>
<td colspan="2" align="left"><strong>Line 116:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((color="red")*(size="small"))'''.</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((color="red")*(size="small"))'''.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">{{botlangbarEN|[[Fr:Les Fonctions SOMME.SI et SOMMEPROD |Français]]}}</td><td>+</td><td style="background: #cfc; font-size: smaller;">{{botlangbarEN|[[Fr:Les Fonctions SOMME.SI et SOMMEPROD |Français<ins style="color: red; font-weight: bold; text-decoration: none;">]] [[It:Le_funzioni_SOMMA.SE_e_MATR.SOMMA.PRODOTTO_in_Calc|Italiano</ins>]]}}</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">[[Category:Tips and Hints]][[Category:NeoOffice]]</td><td> </td><td style="background: #eee; font-size: smaller;">[[Category:Tips and Hints]][[Category:NeoOffice]]</td></tr>
</table>Valterbhttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=12710&oldid=prevSamwise: /* Formulas and Array Formulas */ of the range in the range -> of the range in cells2007-11-15T20:00:54Z<p><span class="autocomment">Formulas and Array Formulas -</span> of the range in the range -> of the range in cells</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 20:00, 15 November 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 4:</strong></td>
<td colspan="2" align="left"><strong>Line 4:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press {{key|Command-Shift-Return}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br></td><td> </td><td style="background: #eee; font-size: smaller;">An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press {{key|Command-Shift-Return}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">:'''Example''' <br></td><td> </td><td style="background: #eee; font-size: smaller;">:'''Example''' <br></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values. To display the product of 5 and each value of this range in <del style="color: red; font-weight: bold; text-decoration: none;">the range </del> B1:B20, we have two possibilities:<br></td><td>+</td><td style="background: #cfc; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values. To display the product of 5 and each value of this range in <ins style="color: red; font-weight: bold; text-decoration: none;">cells </ins> B1:B20, we have two possibilities:<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">1) Click in cell B1, enter the formula:</td><td> </td><td style="background: #eee; font-size: smaller;">1) Click in cell B1, enter the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=A1*5''' </td><td> </td><td style="background: #eee; font-size: smaller;">::'''=A1*5''' </td></tr>
</table>Samwisehttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=12709&oldid=prevJgd: /* Formulas and Array Formulas */ better wording (Thanks Lorinda, Samwise and Smokey)2007-11-15T18:39:49Z<p><span class="autocomment">Formulas and Array Formulas -</span> better wording (Thanks Lorinda, Samwise and Smokey)</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 18:39, 15 November 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 4:</strong></td>
<td colspan="2" align="left"><strong>Line 4:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press {{key|Command-Shift-Return}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br></td><td> </td><td style="background: #eee; font-size: smaller;">An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press {{key|Command-Shift-Return}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">:'''Example''' <br></td><td> </td><td style="background: #eee; font-size: smaller;">:'''Example''' <br></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values. To display the product of each value <del style="color: red; font-weight: bold; text-decoration: none;">by 5 </del>in the range B1:B20, we have two possibilities:<br></td><td>+</td><td style="background: #cfc; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values. To display the product of <ins style="color: red; font-weight: bold; text-decoration: none;"> 5 and </ins>each value <ins style="color: red; font-weight: bold; text-decoration: none;">of this range </ins>in the range B1:B20, we have two possibilities:<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">1) Click in cell B1, enter the formula:</td><td> </td><td style="background: #eee; font-size: smaller;">1) Click in cell B1, enter the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=A1*5''' </td><td> </td><td style="background: #eee; font-size: smaller;">::'''=A1*5''' </td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=12708&oldid=prevJgd: /* SUMPRODUCT Function */ better word order2007-11-15T15:34:28Z<p><span class="autocomment">SUMPRODUCT Function -</span> better word order</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 15:34, 15 November 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 57:</strong></td>
<td colspan="2" align="left"><strong>Line 57:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(range=criteria)'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(range=criteria)'''</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*Let us count the number of cells which contain odd numbers:</td><td> </td><td style="background: #eee; font-size: smaller;">*Let us count the number of cells which contain odd numbers:</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">:<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>the formula: </td><td>+</td><td style="background: #cfc; font-size: smaller;">:<ins style="color: red; font-weight: bold; text-decoration: none;">In an empty cell, enter </ins>the formula: </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(ISODD(A1:A20))'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(ISODD(A1:A20))'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell, </del>and press {{key|Return}}.</td><td>+</td><td style="background: #cfc; font-size: smaller;">and press {{key|Return}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*Let us count the number of cells which contain numbers greater than the range average:</td><td> </td><td style="background: #eee; font-size: smaller;">*Let us count the number of cells which contain numbers greater than the range average:</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">:<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>the formula:</td><td>+</td><td style="background: #cfc; font-size: smaller;">:<ins style="color: red; font-weight: bold; text-decoration: none;">In an empty cell, enter </ins>the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(A1:A20>AVERAGE(A1:A20))'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(A1:A20>AVERAGE(A1:A20))'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell, </del>and press {{key|Return}}.</td><td>+</td><td style="background: #cfc; font-size: smaller;">and press {{key|Return}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">* Let us count the number of cells which contain numbers which are odd '''and''' greater than the range average:</td><td> </td><td style="background: #eee; font-size: smaller;">* Let us count the number of cells which contain numbers which are odd '''and''' greater than the range average:</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">:<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>the formula:</td><td>+</td><td style="background: #cfc; font-size: smaller;">:<ins style="color: red; font-weight: bold; text-decoration: none;">In an empty cell, enter </ins>the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20)))'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20)))'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell, </del>and press {{key|Return}}. </td><td>+</td><td style="background: #cfc; font-size: smaller;">and press {{key|Return}}. </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"> </td><td> </td><td style="background: #eee; font-size: smaller;"> </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">3) To add the numbers in a range which meet some criteria.</td><td> </td><td style="background: #eee; font-size: smaller;">3) To add the numbers in a range which meet some criteria.</td></tr>
<tr><td colspan="2" align="left"><strong>Line 81:</strong></td>
<td colspan="2" align="left"><strong>Line 81:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">* Adding the odd numbers of a range:</td><td> </td><td style="background: #eee; font-size: smaller;">* Adding the odd numbers of a range:</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">*:<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>one of the following formulas:</td><td>+</td><td style="background: #cfc; font-size: smaller;">*:<ins style="color: red; font-weight: bold; text-decoration: none;">In an empty cell, enter </ins>one of the following formulas:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(A1:A20;ISODD(A1:A20))'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(A1:A20;ISODD(A1:A20))'''</td></tr>
<tr><td colspan="2" align="left"><strong>Line 87:</strong></td>
<td colspan="2" align="left"><strong>Line 87:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">or</td><td> </td><td style="background: #eee; font-size: smaller;">or</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((A1:A20)*(ISODD(A1:A20)))'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((A1:A20)*(ISODD(A1:A20)))'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell, </del>and press {{key|Return}}.</td><td>+</td><td style="background: #cfc; font-size: smaller;">and press {{key|Return}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">* Adding the numbers greater than the range average:</td><td> </td><td style="background: #eee; font-size: smaller;">* Adding the numbers greater than the range average:</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">*:<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>the formula:</td><td>+</td><td style="background: #cfc; font-size: smaller;">*:<ins style="color: red; font-weight: bold; text-decoration: none;">In an empty cell, enter </ins>the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(A1:A20;A1:A20>AVERAGE(A1:A20))'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT(A1:A20;A1:A20>AVERAGE(A1:A20))'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell, </del>and press {{key|Return}}.</td><td>+</td><td style="background: #cfc; font-size: smaller;">and press {{key|Return}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">* Adding the numbers odd '''and''' greater than the range average:</td><td> </td><td style="background: #eee; font-size: smaller;">* Adding the numbers odd '''and''' greater than the range average:</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">*:<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>the formula:</td><td>+</td><td style="background: #cfc; font-size: smaller;">*:<ins style="color: red; font-weight: bold; text-decoration: none;">In an empty cell, enter </ins>the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20))*(A1:A20))'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20))*(A1:A20))'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell, </del>and press {{key|Return}}.</td><td>+</td><td style="background: #cfc; font-size: smaller;">and press {{key|Return}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">'''Note''' Defining cell ranges can be helpful. <br></td><td> </td><td style="background: #eee; font-size: smaller;">'''Note''' Defining cell ranges can be helpful. <br></td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=12707&oldid=prevJgd: /* Formulas and Array Formulas */ better wording (Thanks Samwise)2007-11-15T15:28:11Z<p><span class="autocomment">Formulas and Array Formulas -</span> better wording (Thanks Samwise)</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 15:28, 15 November 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 4:</strong></td>
<td colspan="2" align="left"><strong>Line 4:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press {{key|Command-Shift-Return}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br></td><td> </td><td style="background: #eee; font-size: smaller;">An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press {{key|Command-Shift-Return}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">:'''Example''' <br></td><td> </td><td style="background: #eee; font-size: smaller;">:'''Example''' <br></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values. To display the product of <del style="color: red; font-weight: bold; text-decoration: none;">these individal values </del>by 5 in the range B1:B20, we have two possibilities:<br></td><td>+</td><td style="background: #cfc; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values. To display the product of <ins style="color: red; font-weight: bold; text-decoration: none;">each value </ins>by 5 in the range B1:B20, we have two possibilities:<br></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">1) Click in cell B1, enter the formula:</td><td> </td><td style="background: #eee; font-size: smaller;">1) Click in cell B1, enter the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=A1*5''' </td><td> </td><td style="background: #eee; font-size: smaller;">::'''=A1*5''' </td></tr>
</table>Jgdhttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=12706&oldid=prevLorinda: /* SUMIF Function */ a little polish2007-11-15T14:06:01Z<p><span class="autocomment">SUMIF Function -</span> a little polish</p>
<table border='0' width='98%' cellpadding='0' cellspacing='4' style="background-color: white;">
<tr>
<td colspan='2' width='50%' align='center' style="background-color: white;">←Older revision</td>
<td colspan='2' width='50%' align='center' style="background-color: white;">Revision as of 14:06, 15 November 2007</td>
</tr>
<tr><td colspan="2" align="left"><strong>Line 21:</strong></td>
<td colspan="2" align="left"><strong>Line 21:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values.</td><td> </td><td style="background: #eee; font-size: smaller;">Let us suppose that the range A1:A20 contains numeric values.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">*To sum the odd numbers in this range, you have to use an auxiliary column, for example B, to show the parity of the numbers in the range A1:A10.<br></td><td> </td><td style="background: #eee; font-size: smaller;">*To sum the odd numbers in this range, you have to use an auxiliary column, for example B, to show the parity of the numbers in the range A1:A10.<br></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">:*<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>the formula : </td><td>+</td><td style="background: #cfc; font-size: smaller;">:*<ins style="color: red; font-weight: bold; text-decoration: none;">In cell B1, enter </ins>the formula : </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=ISODD(A1:A20)'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=ISODD(A1:A20)'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in cell B1, </del>and press {{key|Command-Shift-Return}}. </td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">::</ins>and press {{key|Command-Shift-Return}}. </td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">:*<del style="color: red; font-weight: bold; text-decoration: none;">Enter </del>the formula:</td><td>+</td><td style="background: #cfc; font-size: smaller;">:*<ins style="color: red; font-weight: bold; text-decoration: none;">In an empty cell, enter </ins>the formula:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(B1:B20;1;A1:A20)'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(B1:B20;1;A1:A20)'''</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">or</td><td> </td><td style="background: #eee; font-size: smaller;">or</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(B1:B20;"TRUE";A1:A20)'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(B1:B20;"TRUE";A1:A20)'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell, </del>and press {{key|Return}}.</td><td>+</td><td style="background: #cfc; font-size: smaller;"><ins style="color: red; font-weight: bold; text-decoration: none;">::</ins>and press {{key|Return}}.</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;">*To sum the numbers in the range A1:A20 which are greater than the average of this range, you don't need an auxiliary column, you can enter the formula:</td><td>+</td><td style="background: #cfc; font-size: smaller;">*To sum the numbers in the range A1:A20 which are greater than the average of this range, you don't need an auxiliary column, you can enter the <ins style="color: red; font-weight: bold; text-decoration: none;">following </ins>formula <ins style="color: red; font-weight: bold; text-decoration: none;">in an empty cell</ins>:</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(A1:A20;">"&AVERAGE(A1:A20);A1:A20)'''</td><td> </td><td style="background: #eee; font-size: smaller;">::'''=SUMIF(A1:A20;">"&AVERAGE(A1:A20);A1:A20)'''</td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"><del style="color: red; font-weight: bold; text-decoration: none;">in an empty cell.</del></td><td>+</td><td style="background: #cfc; font-size: smaller;"> </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">'''Note''' – You can replace the '''SUMIF''' function with the array formula '''SUM'''. The syntax of this function is:</td><td> </td><td style="background: #eee; font-size: smaller;">'''Note''' – You can replace the '''SUMIF''' function with the array formula '''SUM'''. The syntax of this function is:</td></tr>
<tr><td colspan="2" align="left"><strong>Line 47:</strong></td>
<td colspan="2" align="left"><strong>Line 47:</strong></td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">in an empty cell, and press {{key|Command-Shift-Return}}.</td><td> </td><td style="background: #eee; font-size: smaller;">in an empty cell, and press {{key|Command-Shift-Return}}.</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;"></td><td> </td><td style="background: #eee; font-size: smaller;"></td></tr>
<tr><td>-</td><td style="background: #ffa; font-size: smaller;"> </td><td colspan="2"> </td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">==SUMPRODUCT Function==</td><td> </td><td style="background: #eee; font-size: smaller;">==SUMPRODUCT Function==</td></tr>
<tr><td> </td><td style="background: #eee; font-size: smaller;">We suppose again that the range A1:A20 contains numeric values.<br></td><td> </td><td style="background: #eee; font-size: smaller;">We suppose again that the range A1:A20 contains numeric values.<br></td></tr>
</table>Lorindahttps://neowiki.neooffice.org/index.php?title=SUMIF_and_SUMPRODUCT_Functions_in_Calc&diff=12703&oldid=prevJgd: new page. Translation of the French page Les Fonctions SOMME.SI et SOMMEPROD2007-11-15T13:38:55Z<p>new page. Translation of the French page Les Fonctions SOMME.SI et SOMMEPROD</p>
<p><b>New page</b></p><div>{{fixme}}<br />
The SUMIF function belongs to the mathematical functions category and the SUMPRODUCT function to the array functions one. They allow you to work with cell ranges which meet some criteria. Before describing them, it's good to explain the difference between a ''function'' (or formula) and an ''array function'' (or array formula).<br />
==Formulas and Array Formulas==<br />
An array formula (or array function) allows you to perform calculations involving values in a matrix, i.e. in a rectangular range of at least two adjacent cells. Such a formula can handle several values simultaneously. It can return several values, so that you don't need to fill a cell range by dragging the pointer. The results of an array formula is another array. To confirm the entry of an array formula, you have to press {{key|Command-Shift-Return}}. The formula is placed between braces. But you cannot create an array formula by manually entering the braces in the formula bar.<br><br />
:'''Example''' <br><br />
Let us suppose that the range A1:A20 contains numeric values. To display the product of these individal values by 5 in the range B1:B20, we have two possibilities:<br><br />
1) Click in cell B1, enter the formula:<br />
::'''=A1*5''' <br />
<br />
and press the {{key|Return}} key. Then fill the range B1:B20 by dragging the pointer. See [[Selecting and Filling a Cell Range]] in NeoWiki.<br />
<br />
2) Click in cell B1, enter the formula:<br />
::'''=A1:A20*5''' <br />
<br />
and press {{key|Command-Shift-Return}}.<br><br />
The range B1:B20 is automatically filled.<br />
<br />
==SUMIF Function==<br />
This function is a mathematical function, not an array formula. It allows you to sum a cell range which constitutes a matrix and meets some criteria. These criteria must be '''a number or a string'''. The syntax is:<br />
::'''=SUMIF(range _to_be_tested;criteria;sum_range)'''<br><br />
Let us suppose that the range A1:A20 contains numeric values.<br />
*To sum the odd numbers in this range, you have to use an auxiliary column, for example B, to show the parity of the numbers in the range A1:A10.<br><br />
:*Enter the formula : <br />
::'''=ISODD(A1:A20)'''<br />
in cell B1, and press {{key|Command-Shift-Return}}. <br />
:*Enter the formula:<br />
::'''=SUMIF(B1:B20;1;A1:A20)'''<br />
or<br />
::'''=SUMIF(B1:B20;"TRUE";A1:A20)'''<br />
in an empty cell, and press {{key|Return}}.<br />
*To sum the numbers in the range A1:A20 which are greater than the average of this range, you don't need an auxiliary column, you can enter the formula:<br />
<br />
::'''=SUMIF(A1:A20;">"&AVERAGE(A1:A20);A1:A20)'''<br />
in an empty cell.<br />
<br />
'''Note''' – You can replace the '''SUMIF''' function with the array formula '''SUM'''. The syntax of this function is:<br />
<br />
::'''=SUM((range=criteria)*(sum_range))''' <br><br />
For example, in the above cases, you can enter:<br />
<br />
::'''=SUM((B1:B20=1)*(A1:A20))'''<br />
<br />
or<br />
<br />
::'''=SUM((A1:A20>AVERAGE(A1:A20))*(A1:A20))'''<br />
<br />
in an empty cell, and press {{key|Command-Shift-Return}}.<br />
<br />
<br />
==SUMPRODUCT Function==<br />
We suppose again that the range A1:A20 contains numeric values.<br><br />
<br />
The '''SUMPRODUCT''' function is an array function As it returns a single number, it can be entered by pressing only the {{key|Return}} key. It can be used:<br><br />
1) To calculate the scalar product of two vectors.<br />
<br />
2) To get the number of cells in a range which meet some criteria.<br />
The syntax is:<br />
::'''=SUMPRODUCT(range=criteria)'''<br />
*Let us count the number of cells which contain odd numbers:<br />
:Enter the formula: <br />
<br />
::'''=SUMPRODUCT(ISODD(A1:A20))'''<br />
in an empty cell, and press {{key|Return}}.<br />
*Let us count the number of cells which contain numbers greater than the range average:<br />
:Enter the formula:<br />
<br />
::'''=SUMPRODUCT(A1:A20>AVERAGE(A1:A20))'''<br />
in an empty cell, and press {{key|Return}}.<br />
* Let us count the number of cells which contain numbers which are odd '''and''' greater than the range average:<br />
:Enter the formula:<br />
::'''=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20)))'''<br />
in an empty cell, and press {{key|Return}}. <br />
<br />
3) To add the numbers in a range which meet some criteria.<br />
The syntax is:<br />
::'''=SUMPRODUCT(sum_range;criteria)'''<br />
<br />
or<br />
<br />
::'''=SUMPRODUCT((sum_range)*(criteria))'''<br><br />
In each case, you can change the order of the factors.<br />
<br />
* Adding the odd numbers of a range:<br />
*:Enter one of the following formulas:<br />
<br />
::'''=SUMPRODUCT(A1:A20;ISODD(A1:A20))'''<br />
<br />
or<br />
::'''=SUMPRODUCT((A1:A20)*(ISODD(A1:A20)))'''<br />
in an empty cell, and press {{key|Return}}.<br />
* Adding the numbers greater than the range average:<br />
*:Enter the formula:<br />
::'''=SUMPRODUCT(A1:A20;A1:A20>AVERAGE(A1:A20))'''<br />
in an empty cell, and press {{key|Return}}.<br />
* Adding the numbers odd '''and''' greater than the range average:<br />
*:Enter the formula:<br />
::'''=SUMPRODUCT((A1:A20>AVERAGE(A1:A20))*(ISODD(A1:A20))*(A1:A20))'''<br />
in an empty cell, and press {{key|Return}}.<br />
<br />
'''Note''' Defining cell ranges can be helpful. <br><br />
Let us suppose that the cells of the range A1:A10 contain the value "red" or the value "green", and that the cells of the range B1:B10 contain the value "big" or the value "small". To count the number of simultaneous occurrences of the values "red" and "small", we can enter the formula:<br />
::'''=SUMPRODUCT(A1:A10="red";B1:B10="small")'''<br />
or<br />
::'''=SUMPRODUCT((A1:A10="red")*(B1:B10="small"))'''<br />
in C1.<br><br />
But we can also define the ranges A1:A10 and B1:B10. To do that:<br />
*Select the range A1:A10.<br />
*Go to the {{menu|Data}} menu and choose {{menu|Define Range…}}.<br />
*In the {{window|Define Range Database}} window which appears, enter a {{section|Name}}, for example "color" (without quotes).<br />
*Clck on {{button|Add}} then on {{button|OK}}.<br />
*Do the same with the range B1:B10 which you can call "size"<br />
*You can now use the formula:<br />
::'''=SUMPRODUCT(color="red";size="small")'''<br />
<br />
or<br />
<br />
::'''=SUMPRODUCT((color="red")*(size="small"))'''.<br />
<br />
{{botlangbarEN|[[Fr:Les Fonctions SOMME.SI et SOMMEPROD |Français]]}}<br />
<br />
[[Category:Tips and Hints]][[Category:NeoOffice]]</div>Jgd