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.
It comes up in this mock test: Excel test for job interviews
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.