Documentation/Calc Functions/SUMIF

From The Document Foundation Wiki
Jump to navigation Jump to search
Other languages:
English • ‎dansk



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:

Returns a real number which is the sum of cells in the range Range that qualify the given Criteria.

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 are written into the formula, it has to be surrounded by double quotes.

SumRange 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 SumRange 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.
Gnome-dialog-warning.svg

Warning:
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