# 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

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