Documentation/How Tos/Calc: SUMIF function

From Apache OpenOffice Wiki
< Documentation‎ | How Tos
Revision as of 09:00, 3 December 2007 by Drking (Talk | contribs)

Jump to: navigation, search


Conditionally sums the contents of cells in a range.


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



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:

COUNTIF function, SUM function, DSUM function, SUMPRODUCT function

Conditional Counting and Summation

Regular Expressions in Calc

Personal tools