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

m |
|||

Line 66: | Line 66: | ||

'''[[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]''' | '''[[Documentation/How_Tos/Regular Expressions in Calc|Regular Expressions in Calc]]''' | ||

+ | [[fr:Documentation/FR/Calc:Fonction SOMME.SI]] |

## Revision as of 20:31, 29 April 2008

## 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**