Calculate Worked hours per week from a client dropdown list

[Garibaldi]

Hello , I am new to excel and only know the basics

I am trying to do a rota for about 40 employees who work either Morning, Lunch,Tea, PM and Nights
With a possibility of seeing upto 6 clients in a shift (Morning, Lunch,Tea, PM)
Visiting Various clients with different needs.
I have a dropdown list in each cell for the (clients) eg - BS , MH , JJ , DD , KB ,WC etc
Client visit times can vary from 15,30,45 minutes to 1,hour and night shift would be 9 Hours.
I can get get it to work for one entry but i am struggling to get more than one to add up
The sum i have used is =(9)*COUNTIF(G5:L5:Q5:V5:AA5,("BS"))

I have used a macro to select multiple items from dropdown list into one cell , this worked fine but i cannot get more than one client eg - "BS" + "MH" + "JJ" etc to add together to give a total hours worked after seven days.

As i said i am only a beginner at excel and any advice will be much appreciated.

[dilipandey]

HI Garibaldi,

welcome to the forum.

Please upload a sample file with your expected results. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

[Garibaldi]

Hello DiliPandey

I have attached a file with a macro code which inserts more than one item from dropdown list into one cell.

Sample A - I cannot get this to add up as there is more than one item (client) selected from the dropdown list.
Is this possible to do ?
Row AB5 is adding up for one client "BS" five night shifts (9hrs) giving 45 hrs

My overall problem is trying to add multiple clients together over seven days to get a weekly total of hours worked

Regards Garibaldi

[dilipandey]

Okay.. see the attached file - yellow highlighted row where I obtained results for multiple clients. Thanks.

Sample A - Staff Rota with macro.xlsm

Regards,
DILIPandey
<click on below * if this helps>

[Garibaldi]

Hello
Thankyou this is a great start.
I have tried to change the {9*= to {0.3* Is there a reason why it does not work when i change this.

Regards Garibaldi

[dilipandey]

while changing, ignore { bracket as this would come automatically when you press key combination ctrl shift enter after changing 9 to 0.3. thanks.



Regards,
DILIPandey
<click on below * if this helps>