The Excel SUMIF function combines the SUM function for addition and the IF function for conditions. It uses logical operators and wildcards to define criteria, then returns the total for the range that meets those criteria.
Excel SUMIF Function Definition
The SUMIF function calculates the sum of a range that meets one specified condition.
The Difference Between SUMIF and SUMIFS
The main difference between functions ending in IF and those ending in IFS is the difference between a “single condition” and “multiple conditions.” In other words, functions that can use multiple criteria end in IFS, while functions that can use only one condition end in IF.
For more information about multiple criteria in addition functions, please see the SUMIFS function.
Function Syntax
The Excel SUMIF function syntax consists of the range to evaluate, the specified criterion, and the range to sum when the criterion is met.
=SUMIF(range, criteria, sum_range)
Practical SUMIF Function Examples
Let’s look at the basic use of the Excel SUMIF function.
Practical SUMIF Function Example – Single Condition
This is the most basic example of using one condition to add values in a specified range.
In the data below, the SUMIF function is used to calculate the sales quantity for the salesperson Steve.
The function syntax used is as follows.
Cell G3: =SUMIF(B3:B13,”Steve”,E3:E13)
Practical SUMIF Function Example – Multiple Conditions
As explained above, the SUMIFS function is generally the better choice for calculating a sum that meets multiple conditions.
However, the SUMIF function can also calculate the sum of values that meet multiple conditions. Let’s look at how this works.
In the data table below, we calculate the sum of values for two salespeople.
The two salesperson names are Steve and John, and the function syntax used is as follows.
Cell G3: =SUM(SUMIF(B3:B13,{“Steve”,”John”},E3:E13))
Practical SUMIF Function Example – Logical Operators
To make full use of the IF function, you need to understand how to use logical operators.
In the data below, we calculate the total sales for products priced at $200 or more.
The function syntax used is as follows.
Cell G3: =SUMIF(D3:D13,”>=200″,E3:E13)
Practical SUMIF Function Example – Wildcards
Along with logical operators, wildcards are essential in conditional functions.
In the data below, we calculate the sales quantities for salespeople whose names begin with S.
Cell G3: =SUMIF(B3:B13,”S*”,E3:E13)
Conclusion
Using the SUMIF function, we covered not only how to calculate a total for values that meet one condition, but also how to calculate a total for multiple conditions. After writing the function, use absolute references to work more efficiently in Excel.