Excel track · Exercise 29 of 86
SUMIF: total one region
Total only the rows that match a region.
Intermediate
Regional sales
Read-only data · answer in B7
In B7, total the North amounts from the sales table in A1:B6.
| A | B | |
|---|---|---|
| 1 | Region | Amount |
| 2 | North | 320 |
| 3 | South | 250 |
| 4 | North | 180 |
| 5 | West | 300 |
| 6 | North | 400 |
| 7 | North total |
Reference answer
What it does
SUMIF asks two things at once: which rows match, and which column gets added. The regions sit in A2:A6, the condition is North, and the amounts sit in B2:B6.
=SUMIF(A2:A6,"North",B2:B6) returns 900 from the three North rows of 320, 180 and 400, while the table holds 1450. The third argument is the one people drop, and dropping it makes SUMIF add the region names instead, which comes back as 0.
Conditions behave as they do in COUNTIF, so ">=200" and "N*" both work. The two ranges are matched by position, so give them the same shape.
Syntax
=SUMIF(range, criteria, [sum_range])Worked example
On Regional sales, =SUMIF(A2:A6,"North",B2:B6) returns 900.
=SUMIF(A2:A6,"North",B2:B6)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The sum range and the criteria range are different shapes, so the rows no longer line up. |
#NAME? | The function is misspelled, or SUMIFS was used with the argument order SUMIF keeps. |
Wrong total | Leaving out the sum range makes SUMIF add the region names. The answer is 0 and Excel reports nothing. |
At work
A flat export turned into a summary by category, without a pivot table.