Total what a customer still owes, and what's overdue
SUM answers one number. SUMIFS answers "sum this, but only where these conditions hold."
Task:
You handle accounts receivable for a small design studio. Eight invoices sit in front of you, each with a customer, a status and how many days overdue it is. In E10, total how much Bramwell Foods currently still owes — their open invoices only. In E11, total the amount of every open invoice that is more than 30 days overdue, across all customers.
Learning Objectives:
- Filter a SUM by more than one condition with SUMIFS
- Combine a text-equality condition and a numeric comparison in the same SUMIFS call
- Notice SUMIFS's argument order — the range you're summing comes before the conditions, unlike COUNTIFS
Solve without hints for +5 XP
Interactive Spreadsheet
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Customer | Status | Days Overdue | Amount |
| 2 | INV-1001 | Bramwell Foods | Paid | 0 | 1200 |
| 3 | INV-1002 | Sculpture Media | Open | 45 | 860 |
| 4 | INV-1003 | Bramwell Foods | Open | 12 | 540 |
| 5 | INV-1004 | Kestrel Logistics | Open | 62 | 2150 |
| 6 | INV-1005 | Bramwell Foods | Open | 51 | 975 |
| 7 | INV-1006 | Sculpture Media | Paid | 0 | 430 |
| 8 | INV-1007 | Kestrel Logistics | Open | 8 | 310 |
| 9 | INV-1008 | Bramwell Foods | Paid | 0 | 690 |
| 10 | Bramwell Foods still owes | ||||
| 11 | Open invoices over 30 days overdue |
What this exercise teaches (contains the answer)
SUMIFS only adds a row's Amount once every criteria range/criteria pair it was given agrees that row qualifies, which is why Bramwell Foods's paid invoices never reach E10 — Status="Open" rules them out before Customer even gets a say. E11 swaps the customer condition for a numeric one on Days Overdue: ">30" is a piece of text just like "Open" is, but SUMIFS reads it as a comparison rather than an exact match, the same operator-in-quotes trick COUNTIFS(...,">4000") used against a threshold elsewhere. Both formulas are the same skeleton — sum this range, but only where these conditions hold — with different conditions plugged in.