Creating a counter to track number of times someone has done something (with a work rota format)

V

Volosonti

New Member
Joined
Nov 26, 2022
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm trying to make a counter so I can track how many times my staff have performed a certain job on the rotas I create. I'm new into the position and they're used to the current form; I'm hoping not to change it. The counter is on the right side of the rota, seen below. I only have access to Excel 2016 at home, but Office 365 at work, so there is flexibility but responses to 365 solutions will be slow as I will have to email the spreadsheet back across.

Ideally, any solution would be able to account for the rotas to repeat downwards. In the example below I have weeks 49-51, but I would like to be able to have record of the whole year on a single, albeit long, sheet.

All feedback is appreciated.

Book1.xlsx
BCDEFGHIJKLMTUVWXYZ
5Week 4905-Dec06-Dec07-Dec08-Dec09-Dec
6Reception 08:30 - 16:30Terry(1)Terry(2)Hermione(3)Terry(1)Terry(2)
7Reception 09:00 - 17:00Hermione(2)Hermione(3)Terry(1)Hermione(2)Hermione(3)
8Reception 11.00 - 19:00Dillon (12-7)(3)Kerry(1)Dillon (2-7)(2)Kerry(3)Kerry(1)
9Phones 09:00 - 17:00SteveSteveSteveSteveSteve
10Phones 09:00 - 17:00JeanJeanJeanJeanJean
11Phones 09:00 - 17:00DomDomDomDomDom
12Corporate 08:30 - 16:30BobBobBobBob
13Corporate 09:00 - 17:00Kerry
14Corporate 09:00 - 17:00
15
16Week 5012-Dec13-Dec14-Dec15-Dec16-Dec
17Reception 08:30 - 16:30Jean(1)Jean(2)Jean(3)Jean(1)Jean(2)NameReception 08:30 - 16:30Reception 09:00 - 17:00Reception 11:00 - 19:00Phones 09:00 - 17:00Corporate 08:30 - 16:30Corporate 09:00 - 17:00
18Reception 09:00 - 17:00Steve(2)Steve(3)Steve(1)Steve(2)Steve(3)Terry
19Reception 11.00 - 19:00Dillon (12-7)(3)Dom(1)Dillon (2-7)(2)Bob(3)Bob(1)Hermione
20Phones 09:00 - 17:00TerryTerryTerryTerryTerryBob
21Phones 09:00 - 17:00KerryKerryKerryKerryKerryJean
22Phones 09:00 - 17:00HermioneHermioneHermioneHermioneHermioneDom
23Corporate 08:30 - 16:30DomDomDomDomJack
24Corporate 09:00 - 17:00BobSteve
25Corporate 09:00 - 17:00Dillon
26Kerry
27Week 5119-Dec20-Dec21-Dec22-Dec23-Dec
28Reception 08:30 - 16:30Dom(1)Dom(2)Dom(3)Dom(1)Dom(2)
29Reception 09:00 - 17:00Jack(2)Jack(3)Terry(1)Jack(2)Jack(3)
30Reception 11.00 - 19:00Dillon (12-7)(3)Terry(1)Jack(2)Terry(3)Terry(1)
31Phones 09:00 - 17:00BobHermioneJeanHermioneSteve
32Phones 09:00 - 17:00KerryBobBobBobBob
33Phones 09:00 - 17:00SteveSteveHermioneJeanJean
34Corporate 08:30 - 16:30JeanJeanSteveSteveHermione
35Corporate 09:00 - 17:00HermioneKerry
36Corporate 09:00 - 17:00Terry
Rota 3
Cell Formulas
RangeFormula
E5,K27,I27,G27,E27,K16,I16,G16,E16,K5,I5,G5E5=C5+1
C16,C27C16=C5+7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C6:Z4999Cell Valuecontains "Nafisa"textNO
C6:Z4999Cell Valuecontains "Kirsten"textNO
C6:Z4999Cell Valuecontains "Matilda"textNO
C6:Z4999Cell Valuecontains "Lorelei"textNO
C6:Z4999Cell Valuecontains "Abigail"textNO
C6:Z4999Cell Valuecontains "Kyle"textNO
C6:Z4999Cell Valuecontains "Chris"textNO
C6:Z4999Cell Valuecontains "Georgia N"textNO
C6:Z4999Cell Valuecontains "Georgia S"textNO
C6:Z4999Cell Valuecontains "Nafisa"textNO
C6:Z4999Cell Valuecontains "Kirsten"textNO
C6:Z4999Cell Valuecontains "Matilda"textNO
C6:Z4999Cell Valuecontains "Lorelei"textNO
C6:Z4999Cell Valuecontains "Abigail"textNO
C6:Z4999Cell Valuecontains "Kyle"textNO
C6:Z4999Cell Valuecontains "Chris"textNO
C6:Z4999Cell Valuecontains "Georgia N"textNO
C6:Z4999Cell Valuecontains "Georgia S"textNO
L28:L30Cellcontains a blank value textNO
L28:L30Cell Valuecontains "Nafisa"textNO
L28:L30Cell Valuecontains "Kirsten"textNO
L28:L30Cell Valuecontains "Matilda"textNO
L28:L30Cell Valuecontains "Lorelei"textNO
L28:L30Cell Valuecontains "Abigail"textNO
L28:L30Cell Valuecontains "Kyle"textNO
L28:L30Cell Valuecontains "Chris"textNO
L28:L30Cell Valuecontains "Georgia N"textNO
L28:L30Cell Valuecontains "Georgia S"textNO
J28:J30Cellcontains a blank value textNO
J28:J30Cell Valuecontains "Nafisa"textNO
J28:J30Cell Valuecontains "Kirsten"textNO
J28:J30Cell Valuecontains "Matilda"textNO
J28:J30Cell Valuecontains "Lorelei"textNO
J28:J30Cell Valuecontains "Abigail"textNO
J28:J30Cell Valuecontains "Kyle"textNO
J28:J30Cell Valuecontains "Chris"textNO
J28:J30Cell Valuecontains "Georgia N"textNO
J28:J30Cell Valuecontains "Georgia S"textNO
H28:H30Cellcontains a blank value textNO
H28:H30Cell Valuecontains "Nafisa"textNO
H28:H30Cell Valuecontains "Kirsten"textNO
H28:H30Cell Valuecontains "Matilda"textNO
H28:H30Cell Valuecontains "Lorelei"textNO
H28:H30Cell Valuecontains "Abigail"textNO
H28:H30Cell Valuecontains "Kyle"textNO
H28:H30Cell Valuecontains "Chris"textNO
H28:H30Cell Valuecontains "Georgia N"textNO
H28:H30Cell Valuecontains "Georgia S"textNO
F28:F30Cellcontains a blank value textNO
F28:F30Cell Valuecontains "Nafisa"textNO
F28:F30Cell Valuecontains "Kirsten"textNO
F28:F30Cell Valuecontains "Matilda"textNO
F28:F30Cell Valuecontains "Lorelei"textNO
F28:F30Cell Valuecontains "Abigail"textNO
F28:F30Cell Valuecontains "Kyle"textNO
F28:F30Cell Valuecontains "Chris"textNO
F28:F30Cell Valuecontains "Georgia N"textNO
F28:F30Cell Valuecontains "Georgia S"textNO
D28:D30Cellcontains a blank value textNO
D28:D30Cell Valuecontains "Nafisa"textNO
D28:D30Cell Valuecontains "Kirsten"textNO
D28:D30Cell Valuecontains "Matilda"textNO
D28:D30Cell Valuecontains "Lorelei"textNO
D28:D30Cell Valuecontains "Abigail"textNO
D28:D30Cell Valuecontains "Kyle"textNO
D28:D30Cell Valuecontains "Chris"textNO
D28:D30Cell Valuecontains "Georgia N"textNO
D28:D30Cell Valuecontains "Georgia S"textNO
C17:L25,C6:L14Cellcontains a blank value textNO
C28:Z4999Cell Valuecontains "Nafisa"textNO
C6:Z4999Cell Valuecontains "Kirsten"textNO
C6:Z4999Cell Valuecontains "Matilda"textNO
C6:Z4999Cell Valuecontains "Lorelei"textNO
M28:Z36,C6:Z27Cell Valuecontains "Abigail"textNO
C6:Z4999Cell Valuecontains "Kyle"textNO
C6:Z4999Cell Valuecontains "Chris"textNO
C6:Z4999Cell Valuecontains "Georgia N"textNO
M28:Z36,C6:Z27Cell Valuecontains "Georgia S"textNO
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about in U18 dragged down & across
Excel Formula: 
=SUMPRODUCT(($B$6:$B$100=U$17)*($C$6:$L$100=$T18))
 
Upvote 0
Not sure if that is from your real data, but if so you should correct in column B
Reception 11.00 - 19:00
to
Reception 11:00 - 19:00
so that it matches the heading in W17

Apart from that, to allow for examples like Dillon (& blank cells in column T) I would suggest this modification for U18.
I have also increased the number of rows covered to be sure to allow for a full year of weekly data that size.

Excel Formula: 
=IF($T18="","",SUMPRODUCT(($B$6:$B$1000=U$17)*(LEFT($C$6:$L$1000&" ",LEN($T18)+1)=$T18&" ")))
 
Last edited:
Upvote 0
Solution
Peter_SSs said:
Not sure if that is from your real data, but if so you should correct in column B
Reception 11.00 - 19:00
to
Reception 11:00 - 19:00
so that it matches the heading in W17

Apart from that, to allow for examples like Dillon (& blank cells in column T) I would suggest this modification for U18.
I have also increased the number of rows covered to be sure to allow for a full year of weekly data that size.

Excel Formula: 
=IF($T18="","",SUMPRODUCT(($B$6:$B$1000=U$17)*(LEFT($C$6:$L$1000&" ",LEN($T18)+1)=$T18&" ")))
Click to expand...
The names have been changed but the format is as published. Good eye.

The formula works perfectly, both you and Fluff are legends. Thank you!
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
322
awoohaw
awoohaw
A
  • Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
276
AquaBuoy
A
Agnarr
  • Question
Employee Work Schedule
Replies
1
Views
234
HighAndWilder
H
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
241
Lukma
L
P
  • Solved
Tough Data Sheet to get Login/Login dates and times.
Replies
7
Views
333
Fluff
Fluff

Forum statistics

Threads
1,215,193
Messages
6,123,566
Members
449,108
Latest member
rache47

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top