Documentation/Calc Functions/SUMIF

Function name:
SUMIF

Category:
Mathematical

Summary:
In its simplest form, SUMIF determines which cells in a range satisfy a specified criterion and calculates the sum of the numbers in those matched cells. In its second form, SUMIF determines which cells in one range satisfy a specified criterion and then calculates the sum of the numbers in the corresponding cells of a second range.

The criterion passed to SUMIF can utilize wildcards or regular expressions.

Syntax:
SUMIF(Range; Criterion[; SumRange])

Returns:
Returns a real number that is the result of adding together the numbers in relevant cells.

Range Argument
Range specifies the cells to be matched and, when the SumRange argument is omitted, the cells to be summed. Range usually takes one of the following forms:


 * A reference to a cell range (for example, A1:A25). If SumRange is omitted, then Range may utilize the reference concatenation operator (~).
 * The name of a named range.
 * The name of a database range.

Criterion argument
Criterion is the criterion for matching, or a cell containing that criterion. Criterion can take one of the following forms:
 * A number, such as 34.5. Dates and logical values (TRUE or FALSE) are treated as numbers.
 * An expression, such as 2/3, SQRT($D$1), or DATE(2021; 11; 1).
 * A text string, such as "golf" or "<>10".

SUMIF looks for cells in Range that are equal to Criterion, unless Criterion is a text string that starts with a comparator (>, <, >=, <=, =, or <>). In the latter case SUMIF compares the cells in Range with the remainder of the text string (interpreted as a number if possible and text otherwise). For example, the condition ">4.5" tests if the content of each cell is greater than the number 4.5, the condition "2021-11-01" tests if the content of each cell is not equal to the specified date.

Criterion supports the following specific behaviors:
 * The string "=" matches empty cells. For example the formula  returns the sum of all values in the range A1:A10 if all cells in the range B1:B10 are empty. Note that "=0" does not match empty cells.
 * The string "<>" matches non-empty cells. For example the formula  returns the sum of all values in the range A1:A10 if there are no empty cells in the range C1:C10.
 * If the value after the <> comparator is not empty, then Criterion matches any cell content except that value, including empty cells.

SumRange argument
SumRange specifies the cells to be summed and takes one of the forms listed for Range. SumRange should normally have the same dimensions as Range – if this is not the case, SUMIF will sum a range of cells starting at the first cell in SumRange and having the same dimensions as Range.

Error conditions

 * If the SumRange argument is provided and the reference concatenation operator is utilized within Range, then SUMIF reports a parameter list error (Err:504).

Details specific to SUMIF function

 * SUMIF allows only one matching criterion. If you need to sum cells based on multiple criteria, use the SUMIFS function.


 * The default matching performed by SUMIF is case-insensitive. However, a case-sensitive match can be carried out when using a regular expression by including a mode modifier "(?-i)" within the regular expression, as demonstrated by one of the examples below.


 * The behavior of SUMIF is affected by several settings available on the dialog ( on macOS).


 * If the checkbox is ticked for Search criteria = and <> must apply to whole cells, then the condition "red" will match only "red"; if unticked it will match "red", "Fred", "red herring".
 * If the checkbox is ticked for Enable wildcards in formulas, the condition will match using wildcards - so for example "b?g" will match "bag", "beg", "big", "bog", and "bug".
 * 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", "rid", and "rod", while "red.*" will match "red", "redraw", and "redden".
 * The setting of the Case sensitive checkbox has no impact on the operation of SUMIF.

Other examples
The examples in this subsection are based on a small database of sales data for sports equipment, with the data organized as in the following table.

Related LibreOffice functions:
COUNTIF

SUM

SUMIFS

ODF standard:
Section 6.16.62, part 2

Equivalent Excel function:
SUMIF