Prepaid packages sell better than single sessions — but only if you can always answer "how many hours do I have left?" Subtract the SUM of hours used from the package.
A 20-hour package with 1.5 + 2 + 1.5 hours used has 15 left — and a renewal conversation starts around 5.
The example
Three students, each with a package size and hours logged per week.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Package hrs | Wk 1 | Wk 2 | Wk 3 | Remaining |
| 2 | Ava | 20 | 1.5 | 2 | 1.5 | 15.0 |
| 3 | Ben | 10 | 1 | 1 | 1 | 7.0 |
| 4 | Chloe | 12 | 2 | 2 | 0 | 8.0 |
The formula
SUM collects the usage; one subtraction shows the balance:
How it works
The balance is package minus usage:
B2is the package size the student bought, in hours.SUM(C2:E2)adds every session logged so far — extend the range as weeks pass.- Subtracting gives the hours still on account.
Flag balances at or below a threshold with conditional formatting so renewals never sneak up on you.
Try it: interactive demo
Enter the package size and three weeks of session hours.
Variations
Sessions instead of hours
Count sessions used with COUNT if every session is the same length.
Percent of package used
Show usage as a percent for an at-a-glance dashboard.
Pitfalls & errors
Fix the used-hours range wide enough for the whole term (C2:N2) up front — extending it later in 30 copies of the formula is how balances go wrong.
A negative balance means over-delivery — catch it with conditional formatting before it becomes a billing dispute.
Practice workbook
Frequently asked questions
When should I prompt a renewal?
What if a student goes negative?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class