SUMIF

The SUMIF function adds the values in a list that match a specified condition.

Syntax

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

Arguments

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

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

range - Optional. 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.

Example

sumif([10,20,30], ">50", [cell1, cell2,...cellN])

Returns the sum of cell1 to cell n.

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