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

m |
(→Example:) |
||

Line 40: | Line 40: | ||

=== Example: === | === Example: === | ||

− | + | <tt>'''<nowiki>SUMIF(A1:A9;"<0")</nowiki>'''</tt> | |

− | + | : returns the sum of the negative numbers in <tt>'''A1:A9'''</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>. | |

− | + | ||

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

− | + | ||

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

− | + | ||

− | + | ||

− | + | ||

=== See also: === | === See also: === |

## Revision as of 09:00, 3 December 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

`, and sums the corresponding cells in the range`

**condition**`. If`

**sum_range**`is omitted the cells in`

**sum_range**`are summed.`

**test_range**

` condition` is or refers to a text string which has the following format:

**comparator**where**value**

is one of**comparator**,**>**,**<**,**>=**,**<=**,**=**(if**<>**is omitted**comparator**is assumed );**=**

is the value (number or text) to be compared.**value**

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` “

`” will match only`

**red**`; if unticked it will match`

**red**`,`

**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**`, and "`

**rid**`" will match`

**red.***`,`

**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
contains the text**F1**(without double quotes) returns the sum of the positive numbers in**>=0**.**A1:A9**

**SUMIF(B2:B4; "<"&F2; C2:C4)**

- where
contains**F2**and cells**10**,**B2**,**B3**contain**B4**,**7**,**9**, returns the sum of**11**and**C2**, because cells**C3**and**B2**are less than**B3**.**10**

**SUMIF(D1:D9; "apples"; E1:E9)**

- where cells in
contain either**D1:D9**or**apples**and cells in**pears**contain the corresponding quantities of each fruit, returns the total quantity of**E1:E9**.**apples**

### See also:

**COUNTIF function**,
**SUM function**,
**DSUM function**,
**SUMPRODUCT function**