Operations & Analytics
Student Course Enrollment Tracker: Open Seats, Waitlist & Fill Rate
Tally live course registrations from a student roster using COUNTIF, calculate remaining open seats via MAX(0, Cap - Enrolled) and waitlist overflow via MAX(0, Enrolled - Cap), and classify section status.
Prevent negative seat counts when student demand overflows class capacity!
University registrars, corporate L&D coordinators, and bootcamp schedulers track live registrations in a student roster table while monitoring section capacity on a summary dashboard. Subtracting Cap - Enrolled naively displays confusing negative numbers like "-2 Open Seats" whenever waitlisted students sign up past the room cap!
Combining COUNTIF(Roster_Course, Section_Code) with two asymmetric MAX(0, ...) clamps separates available seats cleanly from waitlist demand: Open_Seats = MAX(0, Cap - COUNTIF($B$2:$B$7, D2)) floors at 0 once the room fills, while Waitlist_Count = MAX(0, COUNTIF($B$2:$B$7, D2) - Cap) activates only when registrations exceed capacity! Pair both with an IF status banner ("Open", "Full — Cap Reached", or "Waitlist Active (+N)")!
| Course Section | Room Cap | Registered Roster COUNTIF | Open Seats / Waitlist Status |
|---|---|---|---|
| FIN-201 (4 Roster Signups) | 3 Seats | 4 Students | 0 Open | Waitlist Active (+1) |
| SQL-105 (2 Roster Signups) | 4 Seats | 2 Students | 2 Open | Open (2 Seats Left) |
| ML-310 (0 Roster Signups) | 5 Seats | 0 Students | 5 Open | Open (5 Seats Left) |
Clamped Remaining Open Seats: MAX(0, Seat_Cap - COUNTIF(Roster, Course))
Counts matching course enrollments in B2:B7 against section D2 and floors remaining seats at zero.
=IF(E2 < 1, "Invalid Seat Cap", MAX(0, E2 - COUNTIF($B$2:$B$7, D2)))Automated Enrollment & Waitlist Overflow Status Banner
Distinguishes Open sections, Exact-Capacity Full sections, and Waitlisted sections with exact overflow count.
=IF(COUNTIF($B$2:$B$7, D2) > E2, "Waitlist Active (+" & (COUNTIF($B$2:$B$7, D2) - E2) & ")", IF(COUNTIF($B$2:$B$7, D2) = E2, "Full — Cap Reached", "Open (" & (E2 - COUNTIF($B$2:$B$7, D2)) & " Left)"))Try the example
Use the original values or change them to test your understanding.