diff options
Diffstat (limited to 'helpcontent2/source/text/scalc/01/func_sumifs.xhp')
| -rw-r--r-- | helpcontent2/source/text/scalc/01/func_sumifs.xhp | 73 |
1 files changed, 73 insertions, 0 deletions
diff --git a/helpcontent2/source/text/scalc/01/func_sumifs.xhp b/helpcontent2/source/text/scalc/01/func_sumifs.xhp new file mode 100644 index 000000000..ec594d56b --- /dev/null +++ b/helpcontent2/source/text/scalc/01/func_sumifs.xhp @@ -0,0 +1,73 @@ +<?xml version="1.0" encoding="UTF-8"?> +<helpdocument version="1.0"> +<!-- + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * +--> + +<meta> + <topic id="textscalc01func_sumifsxml"> + <title id="tit" xml-lang="en-US">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_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> function</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> +</section> +<embed href="text/scalc/00/avail_release.xhp#4.0"/> +<embed href="text/scalc/01/common_func.xhp#sectionsyntax"/> +<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_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> + +<embed href="text/scalc/01/ex_data_stat_func.xhp#par00"/> +<embed href="text/shared/00/00000001.xhp#regulaer"/> +<embed href="text/scalc/01/ex_data_stat_func.xhp#warning01"/> +<embed href="text/scalc/01/ex_data_stat_func.xhp#note01"/> +<embed href="text/scalc/01/ex_data_stat_func.xhp#par01"/> +<embed href="text/scalc/01/ODFF.xhp#odff"/> +<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> +<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> +<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> +<embed href="text/scalc/06/calcsamplefiles.xhp#func_ifs"/> +<section id="relatedtopics"> +<paragraph id="par_id11921178730928" role="paragraph" xml-lang="en-US" localize="false"> +<embedvar href="text/scalc/01/04060106.xhp#sum_head"/>, +<embedvar href="text/scalc/01/04060106.xhp#sumif_head"/> +</paragraph> +<paragraph id="par_id11931178730928" role="paragraph" xml-lang="en-US" 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> + </section> +</body> +</helpdocument> |