Difference between revisions of "Documentation/How Tos/Calc: SUMIF function"

From Apache OpenOffice Wiki
Jump to: navigation, search
(9 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
__NOTOC__
+
{{Documentation/MasterTOC
 +
|bookid=1234'''
 +
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">CALC FUNCTIONS</div>
 +
|ShowParttitle=block|parttitle=[[Documentation/How_Tos/Calc:_Mathematical_functions|<div style="font-size: 140%;">Mathematical Functions]]
 +
|ShowNextPage=block|NextPage=Documentation/How_Tos/Calc:_SUMSQ_function
 +
|ShowPrevPage=block|PrevPage=Documentation/How_Tos/Calc:_SUM_function
 +
|ShowPrevPart=block|PrevPart=Documentation/How_Tos/Calc:_Logical_functions
 +
|ShowNextPart=block|NextPart=Documentation/How_Tos/Calc:_Number_Conversion_functions
 +
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>
  
−
== SUMIF  ==
+
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Trigonometric</div>
−
Conditionally sums the contents of cells in a range.
+
* [[Documentation/How_Tos/Calc:_COS_function|<div style="font-size: 120%;">Cos]]
 +
* [[Documentation/How_Tos/Calc:_SIN_function|<div style="font-size: 120%;">Sin]]
 +
* [[Documentation/How_Tos/Calc:_TAN_function|<div style="font-size: 120%;">Tan]]
 +
* [[Documentation/How_Tos/Calc:_COT_function|<div style="font-size: 120%;">Cot]]
 +
* [[Documentation/How_Tos/Calc:_ACOS_function|<div style="font-size: 120%;">Acos]]
 +
* [[Documentation/How_Tos/Calc:_ACOT_function|<div style="font-size: 120%;">Acot]]
 +
* [[Documentation/How_Tos/Calc:_ASIN_function|<div style="font-size: 120%;">Asin]]
 +
* [[Documentation/How_Tos/Calc:_ATAN_function|<div style="font-size: 120%;">Atan]]
 +
* [[Documentation/How_Tos/Calc:_ATAN2_function|<div style="font-size: 120%;">Atan2]]
 +
* [[Documentation/How_Tos/Calc:_DEGREES_function|<div style="font-size: 120%;">Degrees]]
 +
* [[Documentation/How_Tos/Calc:_RADIANS_function|<div style="font-size: 120%;">Radians]]
 +
* [[Documentation/How_Tos/Calc:_PI_function|<div style="font-size: 120%;">Pi]]
  
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Hyperbolic</div>
 +
* [[Documentation/How_Tos/Calc:_COSH_function|<div style="font-size: 120%;">Cosh]]
 +
* [[Documentation/How_Tos/Calc:_SINH_function|<div style="font-size: 120%;">Sinh]]
 +
* [[Documentation/How_Tos/Calc:_TANH_function|<div style="font-size: 120%;">Tanh]]
 +
* [[Documentation/How_Tos/Calc:_COTH_function|<div style="font-size: 120%;">Coth]]
 +
* [[Documentation/How_Tos/Calc:_ACOSH_function|<div style="font-size: 120%;">Acosh]]
 +
* [[Documentation/How_Tos/Calc:_ACOTH_function|<div style="font-size: 120%;">Acoth]]
 +
* [[Documentation/How_Tos/Calc:_ASINH_function|<div style="font-size: 120%;">Asinh]]
 +
* [[Documentation/How_Tos/Calc:_ATANH_function|<div style="font-size: 120%;">Atanh]]
  
−
=== Syntax: ===
+
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Rounding and remainders</div>
−
<tt>'''SUMIF(test_range; condition; sum_range)'''</tt>
+
* [[Documentation/How_Tos/Calc:_TRUNC_function|<div style="font-size: 120%;">Trunc]]
 +
* [[Documentation/How_Tos/Calc:_ROUND_function|<div style="font-size: 120%;">Round]]
 +
* [[Documentation/How_Tos/Calc:_ROUNDDOWN_function|<div style="font-size: 120%;">Rounddown]]
 +
* [[Documentation/How_Tos/Calc:_ROUNDUP_function|<div style="font-size: 120%;">Roundup]]
 +
* [[Documentation/How_Tos/Calc:_CEILING_function|<div style="font-size: 120%;">Ceiling]]
 +
* [[Documentation/How_Tos/Calc:_FLOOR_function|<div style="font-size: 120%;">Floor]]
 +
* [[Documentation/How_Tos/Calc:_EVEN_function|<div style="font-size: 120%;">Even]]
 +
* [[Documentation/How_Tos/Calc:_ODD_function|<div style="font-size: 120%;">Odd]]
 +
* [[Documentation/How_Tos/Calc:_MROUND_function|<div style="font-size: 120%;">Mround]]
 +
* [[Documentation/How_Tos/Calc:_INT_function|<div style="font-size: 120%;">Int]]
 +
* [[Documentation/How_Tos/Calc:_QUOTIENT_function|<div style="font-size: 120%;">Quotient]]
 +
* [[Documentation/How_Tos/Calc:_MOD_function|<div style="font-size: 120%;">Mod]]
  
−
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.
+
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Logarithm/Powers</div>
 +
* [[Documentation/How_Tos/Calc:_EXP_function|<div style="font-size: 120%;">Exp]]
 +
* [[Documentation/How_Tos/Calc:_POWER_function|<div style="font-size: 120%;">Power]]
 +
* [[Documentation/How_Tos/Calc:_LOG_function|<div style="font-size: 120%;">Log]]
 +
* [[Documentation/How_Tos/Calc:_LN_function|<div style="font-size: 120%;">Ln]]
 +
* [[Documentation/How_Tos/Calc:_LOG10_function|<div style="font-size: 120%;">Log10]]
  
 +
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Bessel functions</div>
 +
* [[Documentation/How_Tos/Calc:_BESSELI_function|<div style="font-size: 120%;">Besseli]]
 +
* [[Documentation/How_Tos/Calc:_BESSELJ_function|<div style="font-size: 120%;">Besselj]]
 +
* [[Documentation/How_Tos/Calc:_BESSELK_function|<div style="font-size: 120%;">Besselk]]
 +
* [[Documentation/How_Tos/Calc:_BESSELY_function|<div style="font-size: 120%;">Bessely]]
  
−
<tt>'''condition'''</tt> is or refers to a text string which has the following format:
+
<div style="font-size: 140%; border-style: outset outset outset none; border-color:#DCE9FC;">Miscellaneous</div>
 +
* [[Documentation/How_Tos/Calc:_ABS_function|<div style="font-size: 120%;">Abs]]
 +
* [[Documentation/How_Tos/Calc:_COMBIN_function|<div style="font-size: 120%;">Combin]]
 +
* [[Documentation/How_Tos/Calc:_COMBINA_function|<div style="font-size: 120%;">Combina]]
 +
* [[Documentation/How_Tos/Calc:_CONVERT_function|<div style="font-size: 120%;">Convert]]
 +
* [[Documentation/How_Tos/Calc:_CONVERT_ADD_function|<div style="font-size: 120%;">Convert Add]]
 +
* [[Documentation/How_Tos/Calc:_COUNTBLANK_function|<div style="font-size: 120%;">Countblank]]
 +
* [[Documentation/How_Tos/Calc:_COUNTIF_function|<div style="font-size: 120%;">Countif]]
 +
* [[Documentation/How_Tos/Calc:_DELTA_function|<div style="font-size: 120%;">Delta]]
 +
* [[Documentation/How_Tos/Calc:_ERF_function|<div style="font-size: 120%;">Erf]]
 +
* [[Documentation/How_Tos/Calc:_ERFC_function|<div style="font-size: 120%;">Erfc]]
 +
* [[Documentation/How_Tos/Calc:_FACT_function|<div style="font-size: 120%;">Fact]]
 +
* [[Documentation/How_Tos/Calc:_FACTDOUBLE_function|<div style="font-size: 120%;">Factdouble]]
 +
* [[Documentation/How_Tos/Calc:_GCD_function|<div style="font-size: 120%;">Gcd]]
 +
* [[Documentation/How_Tos/Calc:_GCD_ADD_function|<div style="font-size: 120%;">Gcd Add]]
 +
* [[Documentation/How_Tos/Calc:_GESTEP_function|<div style="font-size: 120%;">Gestep]]
 +
* [[Documentation/How_Tos/Calc:_LCM_function|<div style="font-size: 120%;">Lcm]]
 +
* [[Documentation/How_Tos/Calc:_LCM_ADD_function|<div style="font-size: 120%;">Lcm Add]]
 +
* [[Documentation/How_Tos/Calc:_MULTINOMIAL_function|<div style="font-size: 120%;">Multinomial]]
 +
* [[Documentation/How_Tos/Calc:_PRODUCT_function|<div style="font-size: 120%;">Product]]
 +
* [[Documentation/How_Tos/Calc:_RAND_function|<div style="font-size: 120%;">Rand]]
 +
* [[Documentation/How_Tos/Calc:_RANDBETWEEN_function|<div style="font-size: 120%;">Randbetween]]
 +
* [[Documentation/How_Tos/Calc:_SERIESSUM_function|<div style="font-size: 120%;">Seriessum]]
 +
* [[Documentation/How_Tos/Calc:_SIGN_function|<div style="font-size: 120%;">Sign]]
 +
* [[Documentation/How_Tos/Calc:_SQRT_function|<div style="font-size: 120%;">Sqrt]]
 +
* [[Documentation/How_Tos/Calc:_SQRTPI_function|<div style="font-size: 120%;">Sqrtpi]]
 +
* [[Documentation/How_Tos/Calc:_SUBTOTAL_function|<div style="font-size: 120%;">Subtotal]]
 +
* [[Documentation/How_Tos/Calc:_SUM_function|<div style="font-size: 120%;">Sum]]
 +
* [[Documentation/How_Tos/Calc:_SUMIF_function|<div style="font-size: 120%; border-style: double; border-color:#778899;">Sumif]]
 +
* [[Documentation/How_Tos/Calc:_SUMSQ_function|<div style="font-size: 120%;">Sumsq]]
 +
}}__TOC__
  
−
:<tt>'''comparator'''</tt> <tt>'''value'''</tt> where
+
== SUMIF  ==
 +
Conditionally sums the contents of cells in a range.
  
−
::<tt>'''comparator'''</tt> is one of <tt>'''>'''</tt>, <tt>'''<nowiki><</nowiki>'''</tt>, <tt>'''>='''</tt>, <tt>'''<nowiki><=</nowiki>'''</tt>, <tt>'''<nowiki>=</nowiki>'''</tt>, <tt>'''<nowiki><></nowiki>'''</tt> (if <tt>'''comparator'''</tt> is omitted <tt>'''<nowiki>=</nowiki>'''</tt> is assumed );
+
=== Syntax: ===
 +
<tt>'''SUMIF(test_range; condition; sum_range)'''</tt>
  
−
::<tt>'''value'''</tt> is the value (number or text) to be compared.
+
: 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.  
  
−
For example the condition “<tt>'''>4'''</tt>” tests if the contents of cells are greater than <tt>'''4'''</tt>.
+
: <tt>'''condition'''</tt> may be:
  
 +
:: a number, such as <tt>'''34.5'''</tt>
 +
:: an expression, such as <tt>'''2/3'''</tt> or <tt>'''SQRT(B5)'''</tt>
 +
:: a text string
  
−
Facilities to compare text for equality or inequality (<tt>'''<nowiki>=</nowiki>'''</tt> or <tt>'''<nowiki><></nowiki>'''</tt>) are very powerful, but some care must be taken. You must choose the desired behaviour from the '''Tools menu – Options - OpenOffice.org Calc - Calculate''':
+
: <tt>'''SUMIF'''</tt> looks for cells in  <tt>'''test_range'''</tt> that are equal to <tt>'''condition'''</tt>, unless <tt>'''condition'''</tt> is a text string that starts with a comparator:
  
 +
:: <tt>'''&gt;'''</tt>, <tt>'''&lt;'''</tt>, <tt>'''&gt;='''</tt>, <tt>'''&lt;='''</tt>, <tt>'''='''</tt>, <tt>'''&lt;&gt;'''</tt>
  
−
If the checkbox is ticked for ''<nowiki>search criteria = and <> must apply to whole cells</nowiki>'', then the <tt>'''condition'''</tt> “<tt>'''red'''</tt>” will match only <tt>'''red'''</tt><nowiki>; if unticked it will match </nowiki><tt>'''red'''</tt>, <tt>'''Fred'''</tt>, <tt>'''red herring'''</tt>.
+
: 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>.
  
−
If the checkbox is ticked for ''Enable regular expressions in formulas'', the <tt>'''condition'''</tt> will match using [[Documentation/How_Tos/Regular Expressions in Calc|regular expressions]] - so for example "r.d" 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>.
 
  
 +
: It can be very important to check the settings on the '''Tools menu – Options - OpenOffice.org Calc - Calculate''' dialog:
  
−
At present the checkbox for ''Case sensitive'' has no effect (no attention is paid to case) - but this may be changed in future, so please do not rely on this behaviour.
+
:: 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 unticked 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>.
  
−
Blank (empty) cells in <tt>'''test_range'''</tt> are ignored (they never satisfy the condition).
+
:: 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.
  
  
−
<tt>'''condition'''</tt> can only specify one single condition. See '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' for more information.
+
: Blank (empty) cells in <tt>'''test_range'''</tt> are ignored (they never satisfy the condition).
  
 +
: <tt>'''condition'''</tt> can only specify one single condition. See [[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]] for ways to specify multiple conditions.
  
 
=== Example: ===
 
=== Example: ===
  
−
<tt>'''<nowiki>SUMIF(A1:A9;"<0")</nowiki>'''</tt>
+
<tt>'''SUMIF(A1:A9;"&lt;0")'''</tt>
 
: returns the sum of the negative numbers in <tt>'''A1:A9'''</tt>.
 
: returns the sum of the negative numbers in <tt>'''A1:A9'''</tt>.
  
Line 48: Line 136:
 
: where <tt>'''F1'''</tt> contains the text <tt>'''>=0'''</tt> (without double quotes) returns the sum of the positive numbers in <tt>'''A1:A9'''</tt>.
 
: where <tt>'''F1'''</tt> contains the text <tt>'''>=0'''</tt> (without double quotes) returns the sum of the positive numbers in <tt>'''A1:A9'''</tt>.
  
−
<tt>'''<nowiki>SUMIF(B2:B4; "<"&F2; C2:C4)</nowiki>'''</tt>
+
<tt>'''SUMIF(B2:B4; "&lt;"&F2; C2:C4)'''</tt>
 
: where <tt>'''F2'''</tt> contains <tt>'''10'''</tt> and cells <tt>'''B2'''</tt>, <tt>'''B3'''</tt>, <tt>'''B4'''</tt> contain <tt>'''7'''</tt>, <tt>'''9'''</tt>, <tt>'''11'''</tt>, returns the sum of <tt>'''C2'''</tt> and <tt>'''C3'''</tt>, because cells <tt>'''B2'''</tt> and <tt>'''B3'''</tt> are less than <tt>'''10'''</tt>.
 
: where <tt>'''F2'''</tt> contains <tt>'''10'''</tt> and cells <tt>'''B2'''</tt>, <tt>'''B3'''</tt>, <tt>'''B4'''</tt> contain <tt>'''7'''</tt>, <tt>'''9'''</tt>, <tt>'''11'''</tt>, returns the sum of <tt>'''C2'''</tt> and <tt>'''C3'''</tt>, because cells <tt>'''B2'''</tt> and <tt>'''B3'''</tt> are less than <tt>'''10'''</tt>.
  
Line 54: Line 142:
 
: where cells in <tt>'''D1:D9'''</tt> contain either <tt>'''apples'''</tt> or <tt>'''pears'''</tt> and cells in <tt>'''E1:E9'''</tt> contain the corresponding quantities of each fruit, returns the total quantity of <tt>'''apples'''</tt>.
 
: where cells in <tt>'''D1:D9'''</tt> contain either <tt>'''apples'''</tt> or <tt>'''pears'''</tt> and cells in <tt>'''E1:E9'''</tt> contain the corresponding quantities of each fruit, returns the total quantity of <tt>'''apples'''</tt>.
  
 +
<i>Advanced topic:</i>
  
−
=== See also: ===
+
<tt>'''SUMPRODUCT(D1:D9="Apples"; E1:E9)'''</tt>.
−
'''[[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF function]]''',
+
:returns the sum of entries in <tt>'''E1:E9'''</tt> whose corresponding entry in <tt>'''D1:D9'''</tt> matches <tt>'''Apples'''</tt>, with case sensitivity (entries of <tt>'''apples'''</tt> and <tt>'''APPLES'''</tt> would not match). See [[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]] for details.
−
'''[[Documentation/How_Tos/Calc: SUM function|SUM function]]''',
+
−
'''[[Documentation/How_Tos/Calc: DSUM function|DSUM function]]''',
+
−
'''[[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT function]]'''
+
−
 
+
−
'''[[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]'''
+
  
−
'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''
+
{{SeeAlso|EN|
 +
* [[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF]]
 +
* [[Documentation/How_Tos/Calc: SUM function|SUM]]
 +
* [[Documentation/How_Tos/Calc: DSUM function|DSUM]]
 +
* [[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]]
 +
* [[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]
 +
* [[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]
 +
* [[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]
 +
* [[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/Regular Expressions in Calc|Regular Expressions in Calc]]'''
 
 
[[fr:Documentation/FR/Calc:Fonction SOMME.SI]]
 
[[fr:Documentation/FR/Calc:Fonction SOMME.SI]]
 +
[[Category: Documentation/Reference/Calc/Mathematical functions]]

Revision as of 12:49, 16 July 2018


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 unticked 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
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_SUMIF_function&oldid=243264"
Views
Personal tools
Navigation
Tools
In other languages