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.
- All Excel versions
|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.|
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.
Note: SUMIF function is not case-sensitive. So, "FIRE" and "fire" will yield the same results.
- 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.
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.