The Fixing SUMIF guide
SUMIF rarely shows an error when it's wrong. Check three things: both ranges cover the same rows, they're locked with $ before you fill down, and the label you match has no stray spaces: =SUMIF($A$2:$A$15, E2, $C$2:$C$15).
The checklist
| Symptom | Cause | Fix |
|---|---|---|
| Totals are plausible but wrong | The sum range starts on another row: A2:A15 with C3:C16 | Same rows for both |
| The first row is right, the rest come up short | Ranges slide down when filled | Lock them: $A$2:$A$15 |
| One region shows 0 | A space in the label or the data | Retype it, or TRIM(E4) |
| Some amounts are skipped | Numbers stored as text | Convert them |
An offset sum range
SUMIF pairs the first cell of the range with the first cell of the sum range, and so on down. If the sum range starts a row lower, every match adds the amount on the row below, and the last one can even pick up the total under the data. Excel gives no warning: SUMIF just uses the sum range's top-left cell and the range's size.
Check it adds up
Put a total of the regional totals next to the total of all sales. If every row has a region, the two should be equal. When they aren't, one of the problems above is the reason.
Common mistakes
- Fixing the first row only. The ranges in the rows below still slide; fill the fixed formula down.
- Typing the totals in. They stop matching the data at the next export.
- Swapping the ranges. SUMIF looks in the first range and adds the third. See SUMIF.
FAQ
- Why is my SUMIF not working?
- Check that the criteria range and the sum range cover the same rows, that they are locked with $ if you filled the formula, and that the criteria text matches the data exactly, spaces included.
- Why does SUMIF return 0?
- Nothing matched: often a space at the end of the label or the data, a typo, or ranges swapped so SUMIF adds up text.
- What happens if the SUMIF sum range is a different size?
- Excel starts at the first cell of the sum range and makes it the same size as the criteria range. There is no error, so an offset range quietly adds the wrong rows.
- Why does SUMIF give the wrong total after filling down?
- The ranges weren't locked, so each row looks at a range one row lower. Use $A$2:$A$15 and $C$2:$C$15.



































































