SUMIFS that leaves one value out
PremiumIn F1, total the North orders that were not returned. Give SUMIFS two conditions: the region "North" and the status "<>Returned".
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Status | Amount | North, not returned | |||
| 2 | North | Delivered | 640 | ||||
| 3 | South | Delivered | 310 | ||||
| 4 | North | Returned | 220 | ||||
| 5 | North | Shipped | 480 | ||||
| 6 | West | Returned | 150 | ||||
| 7 | North | Delivered | 390 | ||||
| 8 | South | Shipped | 270 | ||||
| 9 | North | Returned | 110 | ||||
| 10 |
Premium lesson
Unlock SUMIFS and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
"<>Returned" keeps every status except Returned, so the North orders that count are the two Delivered ones and the Shipped one: 640 + 480 + 390 = 1,510. Listing the statuses you want instead would miss any new status someone adds later.
Common mistake: Summing only "Delivered". That drops the Shipped order, which wasn't returned either.
The answer
- F1
=SUMIFS(1C2:C9,2A2:A9,3"North",4B2:B9,5"<>Returned")
In plain words: Add up 1the amounts in C2:C9 on the rows where 2the region in A2:A9 is 3North and 4the status in B2:B9 is 5anything but Returned.