Difference between revisions of "Documentation/How Tos/Calc: SUMIF function"
From Apache OpenOffice Wiki
< Documentation | How Tos
m |
|||
| Line 1: | Line 1: | ||
| − | {{ | + | {{DISPLAYTITLE:SUMIF function}} |
| − | + | {{Documentation/CalcFunc MathematicalTOC | |
| − | + | |ShowPrevNext=block | |
| − | + | |PrevPage=Documentation/How_Tos/Calc:_SUM_function | |
| − | | | + | |NextPage=Documentation/How_Tos/Calc:_SUMSQ_function |
| − | + | }}__NOTOC__ | |
| − | | | + | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | }} | + | |
== SUMIF == | == SUMIF == | ||
| Line 98: | Line 12: | ||
<tt>'''SUMIF(test_range; condition; sum_range)'''</tt> | <tt>'''SUMIF(test_range; condition; sum_range)'''</tt> | ||
| − | : This function identifies those cells in the range <tt>'''test_range'''</tt> that meet the <tt>'''condition'''</tt>, and sums the corresponding cells in the range <tt>'''sum_range'''</tt>. If <tt>'''sum_range'''</tt> is omitted the cells in <tt>'''test_range'''</tt> are summed. | + | : This function identifies those cells in the range <tt>'''test_range'''</tt> that meet the <tt>'''condition'''</tt>, and sums the corresponding cells in the range <tt>'''sum_range'''</tt>. If <tt>'''sum_range'''</tt> is omitted, the cells in <tt>'''test_range'''</tt> are summed. |
: <tt>'''condition'''</tt> may be: | : <tt>'''condition'''</tt> may be: | ||
| Line 112: | Line 26: | ||
: In this case <tt>'''SUMIF'''</tt> compares those cells in <tt>'''test_range'''</tt> with the remainder of the text string (interpreted as a number if possible or text otherwise). | : In this case <tt>'''SUMIF'''</tt> compares those cells in <tt>'''test_range'''</tt> with the remainder of the text string (interpreted as a number if possible or text otherwise). | ||
| − | : For example the condition “<tt>'''>4.5'''</tt>” tests if the content of each cell is greater than the number 4.5, and the condition “<tt>'''<dog'''</tt>” tests if the content of each cell would come alphabetically before the text <tt>'''dog'''</tt>. | + | : For example, the condition “<tt>'''>4.5'''</tt>” tests if the content of each cell is greater than the number 4.5, and the condition “<tt>'''<dog'''</tt>” tests if the content of each cell would come alphabetically before the text <tt>'''dog'''</tt>. |
: It can be very important to check the settings on the '''Tools menu – Options - OpenOffice.org Calc - Calculate''' dialog: | : It can be very important to check the settings on the '''Tools menu – Options - OpenOffice.org Calc - Calculate''' dialog: | ||
| − | :: If the checkbox is ticked for ''<nowiki>search criteria = and <> must apply to whole cells</nowiki>'', then the condition “<tt>'''red'''</tt>” will match only <tt>'''red'''</tt><nowiki>; if | + | :: If the checkbox is ticked for ''<nowiki>search criteria = and <> must apply to whole cells</nowiki>'', then the condition “<tt>'''red'''</tt>” will match only <tt>'''red'''</tt><nowiki>; if not ticked it will match </nowiki><tt>'''red'''</tt>, <tt>'''Fred'''</tt>, <tt>'''red herring'''</tt>. |
:: If the checkbox is ticked for ''Enable regular expressions in formulas'', the condition will match using [[Documentation/How_Tos/Regular Expressions in Calc|regular expressions]] - so for example "<tt>'''r.d'''</tt>" will match <tt>'''red'''</tt>, <tt>'''rod'''</tt>, <tt>'''rid'''</tt>, and "<tt>'''red.*'''</tt>" will match <tt>'''red'''</tt>, <tt>'''redraw'''</tt>, <tt>'''redden'''</tt>. | :: If the checkbox is ticked for ''Enable regular expressions in formulas'', the condition will match using [[Documentation/How_Tos/Regular Expressions in Calc|regular expressions]] - so for example "<tt>'''r.d'''</tt>" will match <tt>'''red'''</tt>, <tt>'''rod'''</tt>, <tt>'''rid'''</tt>, and "<tt>'''red.*'''</tt>" will match <tt>'''red'''</tt>, <tt>'''redraw'''</tt>, <tt>'''redden'''</tt>. | ||
| − | :: The checkbox for ''Case sensitive'' has no effect (no attention is paid to case). See the examples for how to achieve a case sensitive match. | + | :: The checkbox for ''Case sensitive'' has no effect (no attention is paid to case). See the examples for how to achieve a case-sensitive match. |
| Line 157: | Line 71: | ||
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] | * [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]] | ||
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]}} | ||
| − | + | [[Category: Documentation/Reference/Calc/Mathematical functions]] | |
[[fr:Documentation/FR/Calc:Fonction SOMME.SI]] | [[fr:Documentation/FR/Calc:Fonction SOMME.SI]] | ||
| − | |||
Latest revision as of 15:48, 31 January 2024
- Mathematical FunctionsTrigonometric
- COSSINTANCOTACOSACOTASINATANATAN2DEGREESRADIANSPIHyperbolic
- COSHSINHTANHCOTHACOSHACOTHASINHATANHRounding and remainders
- TRUNCROUNDROUNDDOWNROUNDUPCEILINGFLOOREVENODDMROUNDINTQUOTIENTMODLogarithm/Powers
- EXPPOWERLOGLNLOG10Bessel functions
- BESSELIBESSELJBESSELKBESSELYMiscellaneous
- ABSCOMBINCOMBINACONVERTCONVERT ADDCOUNTBLANKCOUNTIFDELTAERFERFCFACTFACTDOUBLEGCDGCD ADDGESTEPISEVENISODDLCMLCM ADDMULTINOMIALPRODUCTRANDRANDBETWEENSERIESSUMSIGNSQRTSQRTPISUBTOTAL
SUMIF
Conditionally sums the contents of cells in a range.
Syntax:
SUMIF(test_range; condition; sum_range)
- This function identifies those cells in the range test_range that meet the condition, and sums the corresponding cells in the range sum_range. If sum_range is omitted, the cells in test_range are summed.
- condition may be:
- a number, such as 34.5
- an expression, such as 2/3 or SQRT(B5)
- a text string
- SUMIF looks for cells in test_range that are equal to condition, unless condition is a text string that starts with a comparator:
- >, <, >=, <=, =, <>
- In this case SUMIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).
- For example, the condition “>4.5” tests if the content of each cell is greater than the number 4.5, and the condition “<dog” tests if the content of each cell would come alphabetically before the text dog.
- It can be very important to check the settings on the Tools menu – Options - OpenOffice.org Calc - Calculate dialog:
- If the checkbox is ticked for search criteria = and <> must apply to whole cells, then the condition “red” will match only red; if not ticked it will match red, Fred, red herring.
- If the checkbox is ticked for Enable regular expressions in formulas, the condition will match using regular expressions - so for example "r.d" will match red, rod, rid, and "red.*" will match red, redraw, redden.
- The checkbox for Case sensitive has no effect (no attention is paid to case). See the examples for how to achieve a case-sensitive match.
- Blank (empty) cells in test_range are ignored (they never satisfy the condition).
- condition can only specify one single condition. See Conditional Counting and Summation for ways to specify multiple conditions.
Example:
SUMIF(A1:A9;"<0")
- returns the sum of the negative numbers in A1:A9.
SUMIF(A1:A9; F1)
- where F1 contains the text >=0 (without double quotes) returns the sum of the positive numbers in A1:A9.
SUMIF(B2:B4; "<"&F2; C2:C4)
- where F2 contains 10 and cells B2, B3, B4 contain 7, 9, 11, returns the sum of C2 and C3, because cells B2 and B3 are less than 10.
SUMIF(D1:D9; "apples"; E1:E9)
- where cells in D1:D9 contain either apples or pears and cells in E1:E9 contain the corresponding quantities of each fruit, returns the total quantity of apples.
Advanced topic:
SUMPRODUCT(D1:D9="Apples"; E1:E9).
- returns the sum of entries in E1:E9 whose corresponding entry in D1:D9 matches Apples, with case sensitivity (entries of apples and APPLES would not match). See Conditional Counting and Summation for details.
See Also
-
