SUMIF function can sum up the values in a range of cells that match a single criteria. In this guide, we’re going to show you how to use the SUMIF function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Syntax
Arguments
range | The range of cells that you want to apply the criteria against. |
criteria | The criteria that is applied to range to define which cells to add. |
sum_range | Optional. The range of cells to sum. |
Examples
We're going to be using named ranges our examples. You don't have to do this, we just added them to make it easier to read the formulas.
Example 1
Example 2
Note: SUMIF function is not case-sensitive. So, "FIRE" and "fire" will yield the same results.
Tips
- Use the same number of rows and columns for sum and criteria range arguments.
- Bad Example: =SUMIF (F2:H10,">2014",G2:G15)
- Good Example: =SUMIF(F2:F11,">2014",G2:G11)
- Comparison operators:
Operator | Description | Criteria Sample | Criteria Meaning |
= | Equal to | “=10000” | Equal to 10000 |
<> | Not equal to | “<>10000” | Not equal to 10000 |
> | Greater than | “>10000” | Greater than 10000 |
< | Less than | “>10000” | Less than 10000 |
>= | Greater than or equal to | “>=10000” | Greater than or equal to 10000 |
<= | Less than or equal to | “<=10000” | Less than or equal to 10000 |
? | Takes the place of a single character | “Admin?” | 6-character word starts by “Admin” |
* | Can take the place of any number of characters. | “Admin*” | Any number of character word starts with “Admin” |
~ | Use tilde in front of a question mark or an asterisk to actually find them | “Admin~*” | Equal to "Admin*" |
Note: Wildcards cannot be used for numeric values. Searching a wild card in a range of numeric values will returns no matches.
Issues
#VALUE!
The SUMIF function returns incorrect results when you use it to match strings longer than 255 characters, or with the string #VALUE!.
TRUE and FALSE
TRUE and FALSE values in sum_range are evaluated as numbers. While TRUE is evaluated as 1, FALSE is evaluated as 0. As a result, this condition can yield unexpected results when added like a cell value.