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

m |
m |
||

Line 60: | Line 60: | ||

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

+ | '''[[Documentation/How_Tos/Calc: COUNTIF function|COUNTIF function]]''', | ||

+ | '''[[Documentation/How_Tos/Calc: SUM function|SUM function]]''', | ||

+ | '''[[Documentation/How_Tos/Calc: DSUM function|DSUM function]]''', | ||

+ | '''[[Documentation/How_Tos/Calc: SUMPRODUCT function|SUMPRODUCT function]]''' | ||

+ | |||

'''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | '''[[Documentation/How_Tos/Conditional Counting and Summation|Conditional Counting and Summation]]''' | ||

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

## Revision as of 20:40, 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 |

### See also:

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