The Excel SUMIF function sums the values in a given cell range if they meet the specified condition.
|1||Range||The cell range that the condition is applied to.|
If you don’t use the
|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
Example 1: Simple Excel Sumif formula
A barebones example of a Sumif formula can look like this:
=SUMIF(A1:A3, " >1")
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
argument. When this argument is used, the cells in
Range will only be used to check if they meet the condition. The cells in
will only be used to sum. Here’s a quick example:
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
C2:C4 if their corresponding value in
Range meets the condition. We know that the first and third cell met the condition, so the first and third cell of the
are summed: 10 + 30 = 40. And 40 is returned.
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
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.
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
argument to sum their ages.
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.
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.