Use SUMIF and SUMIFS to sum with conditions. Learn how to use different types of conditions including dates and multiple criteria for a single range.

Sum with Conditions Using the SUMIF Function

Syntax

=SUMIF(range, criteria, sum_range)

The SUMIF function has three arguments:

range = The range of cells that you want to check for a particular criteria.

criteria = The criteria in the form of a number, text, cell reference, expression or function.  There are many different examples – see the section below.

sum_range = Optional.  This specifies the range of cells to be summed.  If this argument is left out then the cells specified by the range argument are used.

 Criteria

Let’s look at some examples of criteria or conditions using the above data.

Please note that the first example above does not have a sum_range argument specified so the range argument is used for the summing also.  Also the date condition is a special case of the function condition.

The SUMIF function is great if you want to sum using a single condition.  But what if you want to sum and have two or more conditions?  What do you do then?  The solution is the SUMIFS function.

Sum with Conditions Using the SUMIFS Function

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ... )

The SUMIFS function has the following arguments:

sum_range = This specifies the range of cells to be summed.

criteria_range1 = The range of cells that relate to the first criteria.

criteria1 = The criteria in the form of a number, text, cell reference, expression or function, as was the case for the SUMIF function.  See below for some more examples.

criteria_range2, criteria2, … = Optional. Additional ranges and their associated criteria. Up to 127 pairs of ranges and their associated criteria are allowed, which should be more than enough for your situation.

Please take note that the order of the arguments is different between the SUMIF and SUMIFS functions.

Criteria

Let’s look at some examples of the SUMIFS function using the same sample data as that used for the SUMIF function above.

In the above examples I specified the range explicitly ($A$2:$A$10) rather than using the named variable for the Date column so as not to create confusion between the named range (Date) and the function Date(year,month,day).

The second example is quite tricky. It shows you how to handle two selection criteria for one particular range, in addition to having another condition.  The two selection criteria are set up as an array which effectively creates two output values for the SUMIFS function, one for each condition combination.  The SUM function surrounding the SUMIFS function is necessary to sum over these two output values to yield a single value answer.

Other Related Functions

There are some functions for averages which have exactly the same syntax as SUMIF and SUMIFS.  The functions are:

• AVERAGEIF(range, criteria, average_range) ,and
• AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, … ).

There are some functions for counting which have a similar but slightly simpler syntax:

• COUNTIF(range, criteria), and
• COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …).

Enjoy using these useful and powerful functions to sum, average or count things up with conditions.