The guide · SUMIF
Syntax
=SUMIF(range, criteria, [sum_range])- range: the cells to check, for example the Region column.
- criteria: what counts as a match. Text, a number, a comparison like
">500", or a cell. - sum_range: the numbers to add, on the same rows. Leave it out to add the checked cells themselves.
A worked example
The sheet lists ten reps with their region in column B and their sales in column C. For the North total, SUMIF goes down B2:B11, and on every row that says North it takes the number from the same row of C2:C11: 420 + 380 + 530 + 670 = 2,000.
For "sales over $500", the cells you check are the ones you add, so two parts are enough: =SUMIF(C2:C11, ">500").
Conditions you can use
| Criteria | Adds the rows where the checked cell… |
|---|---|
"North" | says North (not case-sensitive) |
"<>North" | says anything except North |
">500" | is more than 500 |
"N*" | starts with N |
F1 | equals whatever is in F1 |
">="&F1 | is at least the number in F1 (see criteria in a cell) |
Common mistakes
- Swapping the ranges.
=SUMIF(C2:C11, "North", B2:B11)looks for North among the sales, finds nothing, and returns 0. - Using the SUMIFS order. In SUMIFS the range to add comes first; in SUMIF it comes last.
- Leaving out the sum range when you need it.
=SUMIF(B2:B11, "North")adds the region names, which are text, so the answer is 0. - No quotes around a comparison.
>500needs to be">500".
FAQ
- How do I use SUMIF in Excel?
- Give it the range to check, the condition, and the range to add: =SUMIF(B2:B11,"North",C2:C11) adds the sales in C for every row where B says North.
- Why does my SUMIF return 0?
- Usually the first and last ranges are swapped, the condition is spelled differently from the data, or the numbers to add are stored as text. Check that the first range holds what you search for and the last one holds numbers.
- How do I sum values greater than a number?
- Put the comparison in quotes and leave out the third part: =SUMIF(C2:C11,">500") adds every value in C2:C11 that is more than 500.
- What is the difference between SUMIF and SUMIFS?
- SUMIF checks one condition and takes the range to add last. SUMIFS takes the range to add first and can check several conditions.