diff options
| author | Olivier Hallot <olivier.hallot@libreoffice.org> | 2024-03-23 10:10:10 -0300 |
|---|---|---|
| committer | Olivier Hallot <olivier.hallot@libreoffice.org> | 2024-03-24 10:13:24 +0100 |
| commit | 7786ebca61858926ce0cd3894922dac725c1997b (patch) | |
| tree | 4f80afe937e327b9d809d881a3fb2552dcfbc0fa | |
| parent | 1e3c2cb1ffa55b44e2b77d14ac5f5ce217630b8a (diff) | |
Housekeeping files on SUM/SUMIF/SUMIFS
Change-Id: I07ac3a115a9b3f86ee05f75f0f3bcdd4cdf1ef69
Reviewed-on: https://gerrit.libreoffice.org/c/help/+/165205
Tested-by: Jenkins
Reviewed-by: Olivier Hallot <olivier.hallot@libreoffice.org>
| -rw-r--r-- | source/text/scalc/01/func_sum.xhp | 17 | ||||
| -rw-r--r-- | source/text/scalc/01/func_sumif.xhp | 32 | ||||
| -rw-r--r-- | source/text/scalc/01/func_sumifs.xhp | 40 |
3 files changed, 45 insertions, 44 deletions
diff --git a/source/text/scalc/01/func_sum.xhp b/source/text/scalc/01/func_sum.xhp index ade627b857..cee32e58b9 100644 --- a/source/text/scalc/01/func_sum.xhp +++ b/source/text/scalc/01/func_sum.xhp @@ -11,14 +11,14 @@ <meta> <topic id="calcfuncsum" indexer="include" status="PUBLISH"> - <title id="tit" xml-lang="en-US">SUM Function</title> + <title id="tit">SUM Function</title> <filename>/text/scalc/01/func_sum.xhp</filename> </topic> </meta> <body> <section id="func_sum"> - <bookmark id="bm_id691636401721449" xml-lang="en-US" branch="hid/SC_HID_FUNC_SUMME" localize="false"/> - <bookmark xml-lang="en-US" branch="index" id="id431636401649762"> + <bookmark id="bm_id691636401721449" branch="hid/SC_HID_FUNC_SUMME" localize="false"/> + <bookmark branch="index" id="id431636401649762"> <bookmark_value>SUM function</bookmark_value> <bookmark_value>adding;numbers in cell ranges</bookmark_value> </bookmark> @@ -27,14 +27,15 @@ </section> <embed href="text/scalc/01/ful_func.xhp#func_head_syntax"/> - <paragraph id="par_id3163656" role="code" xml-lang="en-US">SUM(<embedvar href="text/scalc/01/ful_func.xhp#number255_1" markup="keep"/>)</paragraph> + <paragraph id="par_id3163656" role="code">SUM(<embedvar href="text/scalc/01/ful_func.xhp#number255_1" markup="keep"/>)</paragraph> <embed href="text/scalc/01/ful_func.xhp#number255"/> <embed href="text/scalc/01/common_func.xhp#datenumbersequencenote"/> + <embed href="text/scalc/01/ODFF.xhp#odff"/> <embed href="text/scalc/01/ful_func.xhp#func_head_example"/> - <paragraph id="par_id3163704" role="paragraph" xml-lang="en-US"><input>=SUM(2;3;4)</input> returns 9.</paragraph> - <paragraph id="par_id3151740" role="paragraph" xml-lang="en-US"><item type="input">=SUM(A1;A3;B5)</item> calculates the sum of the three cells.</paragraph> - <paragraph id="par_id931636109030406" role="paragraph" xml-lang="en-US"><item type="input">=SUM(A1:E10)</item> calculates the sum of all cells in the A1 to E10 cell range.</paragraph> - <paragraph id="par_id3151756" role="paragraph" xml-lang="en-US">A formula such as <input>=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)</input> may be entered as an <link href="text/scalc/01/04060107.xhp">array formula</link> by pressing the Shift<switchinline select="sys"><caseinline select="MAC">+Command</caseinline><defaultinline>+Ctrl</defaultinline></switchinline>+Enter keys instead of simply pressing the Enter key to finish entering the formula. The formula will then be shown in the Formula bar enclosed in braces and operates by multiplying corresponding elements of the arrays together and returning their sum.</paragraph> + <paragraph id="par_id3163704" role="paragraph"><input>=SUM(2;3;4)</input> returns 9.</paragraph> + <paragraph id="par_id3151740" role="paragraph"><item type="input">=SUM(A1;A3;B5)</item> calculates the sum of the three cells.</paragraph> + <paragraph id="par_id931636109030406" role="paragraph"><item type="input">=SUM(A1:E10)</item> calculates the sum of all cells in the A1 to E10 cell range.</paragraph> + <paragraph id="par_id3151756" role="paragraph">A formula such as <input>=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)</input> may be entered as an <link href="text/scalc/01/04060107.xhp">array formula</link> by pressing the Shift<switchinline select="sys"><caseinline select="MAC">+Command</caseinline><defaultinline>+Ctrl</defaultinline></switchinline>+Enter keys instead of simply pressing the Enter key to finish entering the formula. The formula will then be shown in the Formula bar enclosed in braces and operates by multiplying corresponding elements of the arrays together and returning their sum.</paragraph> <section id="relatedtopics"> <tip id="par_id771677014538669"><link href="https://wiki.documentfoundation.org/Documentation/Calc_Functions/SUM">SUM wiki page</link>.</tip> diff --git a/source/text/scalc/01/func_sumif.xhp b/source/text/scalc/01/func_sumif.xhp index b6c21457d3..5f723e6183 100644 --- a/source/text/scalc/01/func_sumif.xhp +++ b/source/text/scalc/01/func_sumif.xhp @@ -10,44 +10,44 @@ --> <meta> <topic id="textscalc01func_sumifxml" indexer="include" status="PUBLISH"> - <title id="tit" >SUMIF Function</title> + <title id="tit">SUMIF Function</title> <filename>/text/scalc/01/func_sumif.xhp</filename> </topic> </meta> <body> <section id="func_sumif"> - <bookmark xml-lang="en-US" branch="index" id="bm_id3151957"> + <bookmark branch="index" id="bm_id3151957"> <bookmark_value>SUMIF function</bookmark_value> <bookmark_value>adding;specified numbers</bookmark_value> </bookmark> - <bookmark xml-lang="en-US" branch="hid/SC_HID_FUNC_SUMMEWENN" id="bm_id3151969" localize="false"/> + <bookmark branch="hid/SC_HID_FUNC_SUMMEWENN" id="bm_id3151969" localize="false"/> <h1 id="hd_id3151957"><variable id="sumif_head"><link href="text/scalc/01/func_sumif.xhp">SUMIF</link></variable></h1> - <paragraph id="par_id3151986" role="paragraph" xml-lang="en-US"> + <paragraph id="par_id3151986" role="paragraph"> <ahelp hid=".">Determines which values in a range of cells meet a specified criterion, then returns the sum of the matching values. Alternatively the function can determine which values in one range of cells meet a criterion, then return the sum of values in the corresponding cells in a second range of cells.</ahelp></paragraph> </section> <note id="par_id411711032873068">SUMIF can only apply a single criterion to a range. Use <link href="text/scalc/01/func_sumifs.xhp#sumifs_head">SUMIFS</link> if you need to apply multiple criteria.</note> <embed href="text/scalc/01/common_func.xhp#sectionsyntax"/> - <paragraph id="par_id3152028" role="code" xml-lang="en-US"><input>SUMIF(Range; Criterion [;SumRange])</input></paragraph> - <paragraph id="par_id3152043" role="paragraph" xml-lang="en-US"><emph>Range: </emph> The range of cells to which the criterion is applied.</paragraph> + <paragraph id="par_id3152028" role="code"><input>SUMIF(Range; Criterion [;SumRange])</input></paragraph> + <paragraph id="par_id3152043" role="paragraph"><emph>Range: </emph> The range of cells to which the criterion is applied.</paragraph> <paragraph role="paragraph" id="par_id521711134077228">SUMIF supports the reference concatenation operator (~) only in the Range parameter, and only if the optional SumRange parameter is not given.</paragraph> <embed href="text/scalc/01/ex_data_stat_func.xhp#criterion"/> <embed href="text/shared/00/00000001.xhp#regex"/> - <paragraph id="par_id3152083" role="paragraph" xml-lang="en-US"><emph>SumRange: </emph>Optional. The range of cells from which values are summed. If no SumRange is given, the values in the Range are summed.</paragraph> - + <paragraph id="par_id3152083" role="paragraph"><emph>SumRange: </emph>Optional. The range of cells from which values are summed. If no SumRange is given, the values in the Range are summed.</paragraph> + <embed href="text/scalc/01/ODFF.xhp#odff"/> <section id="ex_sumif"> <embed href="text/scalc/01/ex_data_stat_func.xhp#ex_func_average"/> <h4 id="hd_id451710857799382">Simple usage</h4> - <paragraph role="paragraph" id="par_id171711049202378"><input>=SUMIF(C2:C6,">=100")</input></paragraph> + <paragraph role="paragraph" id="par_id171711049202378"><input>=SUMIF(C2:C6,">=100")</input></paragraph> <paragraph role="paragraph" id="par_id381711049216331">Sums only the values from range C2:C6 that are >=100. Returns 370 because cells C2:C3 do not meet the criterion. The values from Range are summed because no SumRange is given.</paragraph> - <paragraph id="par_id6670125" role="paragraph" xml-lang="en-US"><input>=SUMIF(B2:B5,">=20",C2:C6)</input></paragraph> + <paragraph id="par_id6670125" role="paragraph"><input>=SUMIF(B2:B5,">=20",C2:C6)</input></paragraph> <paragraph role="paragraph" id="par_id341710857230552">Sums values from the range B2:B6 only if the corresponding values in the range A1:A5 are >=20. Returns 340 because the fifth and sixth rows do not meet the criterion.</paragraph> <h4 id="hd_id291710867590698">Using regular expressions</h4> <paragraph role="paragraph" id="par_id911710859836541"><input>=SUMIF(A2:A6,"pen",C2:C5)</input></paragraph> - <paragraph role="paragraph" id="par_id31710859838046">Sums values from the range C2:C6 only if the corresponding range in A2:A6 exactly match the letters "pen". Returns 85 because rows A2 and A4:A6 do not meet the criterion.</paragraph> - <paragraph role="paragraph" id="par_id781710871590769"><input>=SUMIF(A2:A6,"pen*",C2:C6)</input></paragraph> - <paragraph role="paragraph" id="par_id431710871624405">Sums the values from the range C2:C6 only if the corresponding cell in range A2:A6 contains the letters "pen". Returns the value 150 because rows A4:A5 do not meet the criterion.</paragraph> + <paragraph role="paragraph" id="par_id31710859838046">Sums values from the range C2:C6 only if the corresponding range in A2:A6 exactly match the letters "pen". Returns 85 because rows A2 and A4:A6 do not meet the criterion.</paragraph> + <paragraph role="paragraph" id="par_id781710871590769"><input>=SUMIF(A2:A6,"pen*",C2:C6)</input></paragraph> + <paragraph role="paragraph" id="par_id431710871624405">Sums the values from the range C2:C6 only if the corresponding cell in range A2:A6 contains the letters "pen". Returns the value 150 because rows A4:A5 do not meet the criterion.</paragraph> <paragraph role="paragraph" id="par_id311711128230771"><input>=SUMIF(ProductName,"pen*",Revenue)</input></paragraph> <paragraph role="paragraph" id="par_id741711128545751">A named range can be given as the Range or SumRange parameter. For example, if the columns in the above table are respectively named "ProductName", "Sales", and "Revenue", the function returns 150. This function will only work as described if you have defined the names of the columns using the <link href="text/scalc/01/04070100.xhp#definenames)">Define Names</link> dialog.</paragraph> <h4 id="hd_id531711029933195">Reference to a cell as a criterion</h4> @@ -57,18 +57,18 @@ </section> <section id="relatedtopics"> <tip id="par_id771677014538669"><link href="https://wiki.documentfoundation.org/Documentation/Calc_Functions/SUMIF">SUMIF wiki page</link>.</tip> - <paragraph id="par_id11921178730928" role="paragraph" xml-lang="en-US" localize="false"> + <paragraph id="par_id11921178730928" role="paragraph" localize="false"> <embedvar href="text/scalc/01/func_sum.xhp#sum_head"/>, <embedvar href="text/scalc/01/func_sumifs.xhp#sumifs_head"/> </paragraph> - <paragraph id="par_id11931178730928" role="paragraph" xml-lang="en-US" localize="false"> + <paragraph id="par_id11931178730928" role="paragraph" localize="false"> <embedvar href="text/scalc/01/04060181.xhp#countif_head"/>, <embedvar href="text/scalc/01/func_countifs.xhp#countifs_head"/>, <embedvar href="text/scalc/01/func_minifs.xhp#minifs_head"/>, <embedvar href="text/scalc/01/func_maxifs.xhp#maxifs_head"/>, <embedvar href="text/scalc/01/func_averageifs.xhp#averageifs_head"/> </paragraph> - <paragraph id="par_id15970172625152" role="paragraph" localize="false" xml-lang="en-US"> + <paragraph id="par_id15970172625152" role="paragraph" localize="false"> <embedvar href="text/shared/01/02100001.xhp#02100001"/></paragraph> </section> </body> diff --git a/source/text/scalc/01/func_sumifs.xhp b/source/text/scalc/01/func_sumifs.xhp index bcab3c7c62..039d1d81bc 100644 --- a/source/text/scalc/01/func_sumifs.xhp +++ b/source/text/scalc/01/func_sumifs.xhp @@ -11,26 +11,26 @@ <meta> <topic id="textscalc01func_sumifsxml" indexer="include" status="PUBLISH"> - <title id="tit" xml-lang="en-US">SUMIFS function</title> + <title id="tit">SUMIFS function</title> <filename>/text/scalc/01/func_sumifs.xhp</filename> </topic> </meta> <body> <section id="sumifs_function"> -<bookmark xml-lang="en-US" branch="hid/SC_HID_FUNC_SUMIFS" id="bm_id657666576665766" localize="false"/> -<bookmark xml-lang="en-US" branch="index" id="bm_id658066580665806"> +<bookmark branch="hid/SC_HID_FUNC_SUMIFS" id="bm_id657666576665766" localize="false"/> +<bookmark branch="index" id="bm_id658066580665806"> <bookmark_value>SUMIFS function</bookmark_value> <bookmark_value>sum;satisfying conditions</bookmark_value> </bookmark> <h1 id="hd_id658866588665886"><variable id="sumifs_head"><link href="text/scalc/01/func_sumifs.xhp">SUMIFS</link></variable></h1> -<paragraph id="par_id659756597565975" role="paragraph" xml-lang="en-US"><ahelp hid="."><variable id="sumifs_des">Returns the sum of the values of cells in a range that meets multiple criteria in multiple ranges.</variable></ahelp></paragraph> +<paragraph id="par_id659756597565975" role="paragraph"><ahelp hid="."><variable id="sumifs_des">Returns the sum of the values of cells in a range that meets multiple criteria in multiple ranges.</variable></ahelp></paragraph> </section> <embed href="text/scalc/00/avail_release.xhp#4.0"/> <embed href="text/scalc/01/ful_func.xhp#func_head_syntax"/> -<paragraph id="par_id11655988824213" role="code" xml-lang="en-US">SUMIFS(<embedvar href="text/scalc/01/ex_data_stat_func.xhp#args" markup="ignore"/>)</paragraph> +<paragraph id="par_id11655988824213" role="code">SUMIFS(<embedvar href="text/scalc/01/ex_data_stat_func.xhp#args" markup="ignore"/>)</paragraph> -<paragraph id="par_id59901690530236" role="paragraph" xml-lang="en-US"><emph>Func_Range</emph> – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the sum.</paragraph> +<paragraph id="par_id59901690530236" role="paragraph"><emph>Func_Range</emph> – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the sum.</paragraph> <embed href="text/scalc/01/ex_data_stat_func.xhp#par00"/> <embed href="text/shared/00/00000001.xhp#regex"/> @@ -41,34 +41,34 @@ <embed href="text/scalc/01/ex_data_stat_func.xhp#ex_func_average"/> <h4 id="hd_id193452436229521">Simple usage</h4> -<paragraph id="par_id94321051525036" role="paragraph" xml-lang="en-US"><item type="input">=SUMIFS(B2:B6;B2:B6;">=20")</item></paragraph> -<paragraph id="par_id28647227259438" role="paragraph" xml-lang="en-US">Calculates the sum of values of the range B2:B6 that are greater than or equal to 20. Returns 75, because the fifth row does not meet the criterion.</paragraph> -<paragraph id="par_id36952767622741" role="paragraph" xml-lang="en-US"><item type="input">=SUMIFS(C2:C6;B2:B6;">=20";C2:C6;">70")</item></paragraph> -<paragraph id="par_id189772445525114" role="paragraph" xml-lang="en-US">Calculates the sum of values of the range C2:C6 that are greater than 70 and correspond to cells of the B2:B6 range with values greater than or equal to 20. Returns 275, because the second and the fifth rows do not meet at least one criterion.</paragraph> +<paragraph id="par_id94321051525036" role="paragraph"><item type="input">=SUMIFS(B2:B6;B2:B6;">=20")</item></paragraph> +<paragraph id="par_id28647227259438" role="paragraph">Calculates the sum of values of the range B2:B6 that are greater than or equal to 20. Returns 75, because the fifth row does not meet the criterion.</paragraph> +<paragraph id="par_id36952767622741" role="paragraph"><item type="input">=SUMIFS(C2:C6;B2:B6;">=20";C2:C6;">70")</item></paragraph> +<paragraph id="par_id189772445525114" role="paragraph">Calculates the sum of values of the range C2:C6 that are greater than 70 and correspond to cells of the B2:B6 range with values greater than or equal to 20. Returns 275, because the second and the fifth rows do not meet at least one criterion.</paragraph> <h4 id="hd_id30455222431067">Using regular expressions and nested functions</h4> -<paragraph id="par_id307691022525348" role="paragraph" xml-lang="en-US"><item type="input">=SUMIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6))</item></paragraph> -<paragraph id="par_id27619246864839" role="paragraph" xml-lang="en-US">Calculates the sum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 255, because the third and the fifth rows do not meet at least one criterion.</paragraph> -<paragraph id="par_id220502883332563" role="paragraph" xml-lang="en-US"><item type="input">=SUMIFS(C2:C6;A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6))</item></paragraph> -<paragraph id="par_id15342189586295" role="paragraph" xml-lang="en-US">Calculates the sum of values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 65, because only second row meets all criteria.</paragraph> +<paragraph id="par_id307691022525348" role="paragraph"><item type="input">=SUMIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6))</item></paragraph> +<paragraph id="par_id27619246864839" role="paragraph">Calculates the sum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 255, because the third and the fifth rows do not meet at least one criterion.</paragraph> +<paragraph id="par_id220502883332563" role="paragraph"><item type="input">=SUMIFS(C2:C6;A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6))</item></paragraph> +<paragraph id="par_id15342189586295" role="paragraph">Calculates the sum of values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 65, because only second row meets all criteria.</paragraph> <h4 id="hd_id8168283329426">Reference to a cell as a criterion</h4> -<paragraph id="par_id50762995519951" role="paragraph" xml-lang="en-US">If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the SUMIFS function. For example, the above function can be rewritten as follows:</paragraph> -<paragraph id="par_id135761606425300" role="paragraph" xml-lang="en-US"><item type="input">=SUMIFS(C2:C6;A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6))</item></paragraph> -<paragraph id="par_id30574750215839" role="paragraph" xml-lang="en-US">If E2 = pen, the function returns 65, because the link to the cell is substituted with its content.</paragraph> +<paragraph id="par_id50762995519951" role="paragraph">If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the SUMIFS function. For example, the above function can be rewritten as follows:</paragraph> +<paragraph id="par_id135761606425300" role="paragraph"><item type="input">=SUMIFS(C2:C6;A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6))</item></paragraph> +<paragraph id="par_id30574750215839" role="paragraph">If E2 = pen, the function returns 65, because the link to the cell is substituted with its content.</paragraph> <embed href="text/scalc/06/calcsamplefiles.xhp#func_ifs"/> <section id="relatedtopics"> <tip id="par_id971677014512029"><link href="https://wiki.documentfoundation.org/Documentation/Calc_Functions/SUMIFS">SUMIFS wiki page</link>.</tip> -<paragraph id="par_id11921178730928" role="paragraph" xml-lang="en-US" localize="false"> +<paragraph id="par_id11921178730928" role="paragraph" localize="false"> <embedvar href="text/scalc/01/func_sum.xhp#sum_head"/>, <embedvar href="text/scalc/01/func_sumif.xhp#sumif_head"/> </paragraph> -<paragraph id="par_id11931178730928" role="paragraph" xml-lang="en-US" localize="false"> +<paragraph id="par_id11931178730928" role="paragraph" localize="false"> <embedvar href="text/scalc/01/func_countifs.xhp#countifs_head"/>, <embedvar href="text/scalc/01/func_minifs.xhp#minifs_head"/>, <embedvar href="text/scalc/01/func_maxifs.xhp#maxifs_head"/>, <embedvar href="text/scalc/01/func_averageifs.xhp#averageifs_head"/>, <embedvar href="text/scalc/01/func_sumifs.xhp#sumifs_head"/> </paragraph> -<paragraph id="par_id15970172625152" role="paragraph" localize="false" xml-lang="en-US"><embedvar href="text/shared/01/02100001.xhp#02100001"/></paragraph> +<paragraph id="par_id15970172625152" role="paragraph" localize="false"><embedvar href="text/shared/01/02100001.xhp#02100001"/></paragraph> </section> </body> </helpdocument> |