Guided project · 5 steps Cellwise Plus
Front office staff hours in Excel
Total each person's week, flag hours over target and find your thinnest day.
You will use: SUM, MAX, COUNTIF, SUMIF, MIN, MATCH, INDEX
Starting data
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Staff | Role | Mon | Tue | Wed | Thu | Fri | Total | Over target | Weekly target hrs | 40 | |
| 2 | Ana | Front desk | 8 | 8 | 8 | 8 | 0 | |||||
| 3 | Marcus | Front desk | 9.5 | 9.5 | 0 | 9.5 | 9.5 | |||||
| 4 | Lena | Billing | 8.5 | 8.5 | 8.5 | 8.5 | 8 | Hours by role | ||||
| 5 | Tom | Billing | 0 | 6 | 6 | 6 | 0 | Front desk | ||||
| 6 | Jade | Scheduler | 7.5 | 0 | 7.5 | 7.5 | 7.5 | Billing | ||||
| 7 | Scheduler | |||||||||||
| 8 | On shift | |||||||||||
| 9 | Thinnest day |
Build it step by step
Step 1
Hours are decimal (9.5 = nine and a half hours). Total Ana's week in H2, then fill to H6.
Step 2
How many hours is each person over the weekly target in L1? Show 0 when they are under. Write it in I2, fill to I6.
Step 3
How many people are working each day? Count the cells above 0 in C8, then fill to G8.
Step 4
Total scheduled hours for each role in L5, using the role name in K5. Fill to L7.
Step 5
Which day has the fewest people on shift? Return the day name from row 1 in L9.