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.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 teachesMay contain 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.