=SUMIF(A2:A6,"North",B2:B6) returns 900. The region column holds three North rows (320, 180 and 400), and SUMIF adds those three numbers and nothing else. The whole table comes to 1450, so the formula is not a total of the range; it is a total of the matching part of it.
Answer
=SUMIF(A2:A6,"North",B2:B6) reads as three separate jobs. A2:A6 is where the condition is checked. "North" is the condition, quoted because it is text. B2:B6 is what gets added when the condition holds.
Leave the third argument out and the formula still runs: =SUMIF(A2:A6,"North") adds the cells in the condition range, which are the words North, so the sum is 0. That silent zero is the most common SUMIF bug, and it is the reason the third argument is worth writing out every time even when it looks optional.
Why it works
- Two ranges, matched by position: row 2 of the condition range is tested, and a match adds row 2 of the sum range. Keep them the same shape. Excel does not complain when they differ, so
A2:A6withB2:B10readsB2:B6. SUMIFS returns#VALUE!instead. - The condition is a value, not a formula, and it can be a comparison or a pattern:
">=200","<>North","N*". Comparison operators live inside the quotes, which surprises people who write>="200". - Matching ignores case.
"north"finds North, so a region column with mixed capitalisation still totals correctly, but two spellings of one region cannot be counted separately by SUMIF alone. - The answer follows the condition only if the condition points at a cell. Typed into the formula it becomes a hardcoded report; written as
A7it becomes a summary you can drag down a list of regions. - One condition is the whole contract. A second condition needs
SUMIFS, whose argument order is different: sum range first, then pairs of condition range and condition. Converting a working SUMIF by guesswork is how people end up with a total of nothing.
The same shape works for the other aggregate questions: COUNTIF counts the matching rows instead of adding them, AVERAGEIF averages them, and MAXIFS finds the largest. All of them ask "which rows match", then do one thing to those rows.
What goes wrong
| What you see | What it means | The fix |
|---|---|---|
| 0 instead of a total | the third argument was left out, so SUMIF added the region names | write the number column: =SUMIF(A2:A6,"North",B2:B6) |
#VALUE! | the condition range and the sum range are different shapes, or the sum range is a typed number rather than cells | make both ranges the same height and both point at cells |
| The total of every region | the condition is a wildcard that matches everything, or the sum range is the whole column | check the quotes: "*" matches every text value |
| A total that is short by one row | one range stops a row before the other | count the rows of each range; they must match |
| A total that never changes | the condition was typed into the formula instead of pointed at | reference the cell that holds the region name |
The first row is the one that costs trust: the cell shows 0, and a 0 gets published. If a SUMIF returns 0, look at the third argument before you look at the data. A missing sum range produces exactly the same screen as a region with no sales.
Practice
The exercise below is a five-row sales table with three North rows in it. Type the source ranges and the condition, and it is graded against the reference. A missing third argument and a mismatched range each come back with their own message.
Try it: exercise 1 of 1
In B7, total the North amounts from the sales table in A1:B6.
That is not the North total. With only two arguments SUMIF adds the region names, which is zero.
Show the answer
Cell B7 holds 900Where to go next
| Page | Why it helps |
|---|---|
| SUMIF explained | The full note on the function, with the SUMIFS difference written out. |
| count the open tickets with COUNTIF | The same two ranges and a different question, when you want the row count rather than the total. |
| count the non-empty cells | The one that counts words, for when you want to know how many cells have anything in them at all. |