SUMIF

Adds values that meet a specified condition to calculate conditional totals based on defined criteria.

The SUMIF function adds the values in a list that meet a specified condition. It evaluates the given range against the defined criteria and includes only those values that satisfy the condition in the total. This function is commonly used in reporting to calculate conditional totals, such as sums above a threshold or values that match specific rules.

Syntax

SUMIF([list],condition,[range])

Arguments

list – An array of numbers to iterate through (Required).

condition – The numbers are summed up if they meet the condition specified in this argument (Required).

range - An array of numbers that will be summed if the numbers in the list array meet the condition. If a range argument is not given, the array or numbers in the list argument are summed up (Optional).

Example

SUMIF([COLUMN1,COLUMN2],"CONDITION")

Example 1:

In this example, we have not passed the 3rd range parameter. If the sum of the highlighted cells exceeds 20m, then the same cells are summed up.

SUMIF without a range parameter

Example 2:

This is another way to use SUMIF without passing the range parameter. If the sum of the actuals and plan exceeds 20m, then the actuals and plan are summed up for each row. When the condition is not met, SUMIF yields 0.

SUMIF for each row without a range parameter