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

From Apache OpenOffice Wiki
Jump to: navigation, search
(See also:)
m (Robot: Automated text replacement %s)
Line 28: Line 28:
 
:: 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 ''<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>.
+
:: 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 35: Line 35:
 
: Blank (empty) cells in <tt>'''test_range'''</tt> are ignored (they never satisfy the condition).
 
: 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.
+
: <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: ===
Line 54: Line 54:
  
 
<tt>'''SUMPRODUCT(D1:D9="Apples"; E1:E9)'''</tt>.  
 
<tt>'''SUMPRODUCT(D1:D9="Apples"; E1:E9)'''</tt>.  
: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.
+
: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.
  
=== See also: ===
+
{{Documentation/SeeAlso|
'''[[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF]]''',
+
* [[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF]]
'''[[Documentation/How_Tos/Calc: SUM function|SUM]]''',
+
* [[Documentation/How_Tos/Calc: SUM function|SUM]]
'''[[Documentation/How_Tos/Calc: DSUM function|DSUM]]''',
+
* [[Documentation/How_Tos/Calc: DSUM function|DSUM]]
'''[[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]]'''
+
* [[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT]]'''
  
'''[[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]'''
+
* [[Documentation/How_Tos/Calc: Mathematical functions|Mathematical functions]]'''
  
'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]'''
+
* [[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/Regular Expressions in Calc|Regular Expressions in Calc]]'''
  
[[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]]}}
  
 
[[fr:Documentation/FR/Calc:Fonction SOMME.SI]]
 
[[fr:Documentation/FR/Calc:Fonction SOMME.SI]]

Revision as of 14:20, 25 February 2009


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.

Template:Documentation/SeeAlso

Personal tools
In other languages