Difference between revisions of "Documentation/How Tos/Calc: SUMIF function"
m |
m |
||
Line 41: | Line 41: | ||
{| border="0" | {| border="0" | ||
− | |<tt>'''<nowiki>SUMIF(A1:A9;"<0")</nowiki>'''</tt> | + | |-valign="top" |
+ | |width="240pt"|<tt>'''<nowiki>SUMIF(A1:A9;"<0")</nowiki>'''</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>. | ||
− | |- | + | |-valign="top" |
|<tt>'''SUMIF(A1:A9; F1) '''</tt> | |<tt>'''SUMIF(A1:A9; F1) '''</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>. | | 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>. | ||
− | |- | + | |-valign="top" |
|<tt>'''<nowiki>SUMIF(B2:B4; "<"&F2; C2:C4)</nowiki>'''</tt> | |<tt>'''<nowiki>SUMIF(B2:B4; "<"&F2; C2:C4)</nowiki>'''</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>. | ||
− | |- | + | |-valign="top" |
|<tt>'''SUMIF(D1:D9; “apples”; E1:E9)'''</tt> | |<tt>'''SUMIF(D1:D9; “apples”; E1:E9)'''</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>. | | 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>. |
Revision as of 20:34, 30 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 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.
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. |