Documentation/Calc Functions/SUMIF

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

SUMIF

Category:

Mathematical

Summary:

Adds the cells specified by a given criteria. This function is used to browse a range when you search for a certain value.

Syntax:

SUMIF(Range; Criteria [; SumRange])

Returns:

Arguments:

Range is the range to which the criteria are to be applied.

Criteria is the cell in which the search criterion is shown, or the search criterion itself. If the criteria is written into the formula, it has to be surrounded by double quotes.

Sum Range is the range from which values are summed. If this parameter has not been indicated, the values found in the Range are summed.

SUMIF supports the reference concatenation operator (~) only in the Criteria parameter, and only if the optional Sum Range parameter is not given.

Additional details:

  • The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in Tools > Options > LibreOffice Calc > Calculate.
  • When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".
  • See COUNTIF() for some more syntax examples that can be used with SUMIF().

Examples:

Formula Description Returns
=SUMIF(A1:A4;"<0") where A1,A2,A3,A4 are 3,-4,-5,-6 sum up only negative numbers -15
=SUMIF(A1:A3;">0";B1:B3) where A1,A2,A3 are 3,-4,5 and B1,B2,B3 are 2,3,4 sums values from the range B1:B3 only if the corresponding values in the range A1:A3 are greater than 0 so here 3,5 are >0 hence 2+4 is returned 6

Related LibreOffice functions:

SUMIFS

SUM

COUNTIF

COUNTIFS

ODF standard:

Section 6.16.62, part 2

Equivalent Excel function:

SUMIF