source/text/scalc/01/func_sum.xhp | 17 +++++++-------
source/text/scalc/01/func_sumif.xhp | 32 ++++++++++++++--------------
source/text/scalc/01/func_sumifs.xhp | 40 +++++++++++++++++------------------
3 files changed, 45 insertions(+), 44 deletions(-)
New commits:
commit 7786ebca61858926ce0cd3894922dac725c1997b
Author: Olivier Hallot <olivier.hal...@libreoffice.org>
AuthorDate: Sat Mar 23 10:10:10 2024 -0300
Commit: Olivier Hallot <olivier.hal...@libreoffice.org>
CommitDate: Sun Mar 24 10:13:24 2024 +0100
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.hal...@libreoffice.org>
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>
