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

From Apache OpenOffice Wiki
Jump to: navigation, search
m (Syntax:: formatting tweak)
m (regex link in text)
Line 29: Line 29:
  
  
If the checkbox is ticked for ''Enable regular expressions in formulas'', the <tt>'''condition'''</tt> will match using 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>.
+
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>.
  
  

Revision as of 00:32, 27 November 2007


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 is or refers to a text string which has the following format:

comparator value

where

comparator is one of >, <, >=, <=, =, <> (if comparator is omitted = is assumed );
value is the value (number or text) to be compared.

For example the condition “>4” tests if the contents of cells are greater than 4.


Facilities to compare text for equality or inequality (= or <>) are very powerful, but some care must be taken. You must choose the desired behaviour from the Tools menu – Options - OpenOffice.org Calc - Calculate:


If the checkbox is ticked for search criteria = and <> must apply to whole cells, then the conditionred” 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.


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.


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 more information.

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.

See also:

Conditional Counting and Summation

Regular Expressions in Calc

Personal tools