The Excel SUMIF function sums the values in a given cell range if they meet the specified condition.
Syntax
=SUMIF(Range, Condition, [Sum range])
| Argument | Argument description | |
|---|---|---|
| 1 | Range | The cell range that the condition is applied to. If you don’t use the Sum range argument, Rangeis summed instead. |
| 2 | Condition | The condition that determines which values are summed. |
| 3 | Sum range (Optional) | The cells that will be summed up. If you don’t use the Sum range argument, Rangeis summed instead. |
Example 1: Simple Excel Sumif formula
A barebones example of a Sumif formula can look like this:
=SUMIF(A1:A3, " >1")
Explanation
The result is 5 because the formula checks for each value in cells A1, A2 and A3 if they meet the condition given. The condition is “> 1”, so values have to be larger than 1.
Let’s walk through the values: 1 is not larger than 1. 2 is larger than 1. And 3 is also larger than 1. So 2 and 3 meet the condition and are added together. 2 + 3 = 5 and therefore the resulting value is 5.
Example 2: Sum range
In the above example, the Range argument is used as both the range that is summed and the range that the condition is applied to.
We can separate these two functionalities using the Sum rangeRange will only be used to check if they meet the condition. The cells in Sum range
Sum rangeRange is “Yes”.Explanation
The returned value is 40 because we are looking in Range B2:B4 for cells that contain the text Yes. There are two cells that meet this condition B2 and B4, the first and third cell in the range.
Then, we sum the values in Sum rangeRange meets the condition. We know that the first and third cell met the condition, so the first and third cell of the Sum range
Example 3: Wildcards and Sumif
The Sumif function allows for all sorts of conditions as Condition argument. They can be texts, numbers and dates. They can be combined with conditional operators or functions that return TRUE or FALSE. For more information on different types of conditions, check out this section of our complete guide on the If function.
Another thing that’s allowed is the use of wildcards in the Conditionargument.
Wildcards are characters like ? and * that allow you to search for an approximate match instead of an exact word match.
Note: for more information on wildcards, check this section about wildcards from our Find and Replace post.
Here’s an example formula where we use the asterisk (*) wildcard to match with a last name regardless of first name.
Explanation
123 is returned because that is the sum of the ages of the members of the Collins family (50 + 51 + 22 = 123).
The condition *Collins matches with any names ending in Collins, regardless of what text is before that. Therefore, we match with every Collins family member and we use the Sum range
Questions
Yes, you can. The easiest way to do this is by using the Sumifs function, which allows for multiple (range, condition) pairs.
An alternative with one range and multiple conditions is to use the regular Sumif function, combined with logical operators (And, Or, Not, Xor).
No, the Sumif and Sumifs functions are not compatible with arrays as input argument. You can use the Sumproduct function as a workaround.
No, Sumif is not case sensitive. So if your condition is “POTTER” you will also find Potter, pOTTER and potter.
Drawbacks
The Excel Sumif function has some drawbacks:
- We’ve mentioned it before: you cannot use arrays instead of cell ranges with the SUMIF or SUMIFS functions.