Staff Scheduler.....can't quite get it right

[Motoxboi26]

Ok excel gurus I need some help since I've about exhaused the base of what I know how to do/have used from other sheets I have/ and or can figure out for myself.

I'm trying to update our staff scheduler to be more comprehensive and help with managing labor costs/budget. I finally figured out how to deduct meal periods using a formula from a scheduler that I had at a different company I think it may be a complicated way to get what I want but it works. (If there is an "X" in the K33:K40 range then it will decuct a meal period of either 30min or 1 hour depending on where they are working)If there is a simpler way I would love to hear it.

Now I am trying to figure out how to make my sheet calculate hours based on shifts assigned ( we schedule using a number system as shift times may fluctuate based on our volumn)

I need to be able to count each occurence of a shift number, then multiply it by the number of hours in the shift for each week to give me a total number of hours. The schedule is a 2 week schedule but i need each week to calculate separately(easy enough to do transferring the formlua and changing the ranges). I've also been trying to unsuccesfully only calculate if there is a name on the schedule in the name slot

This is what I was trying to use
In Cell S7
=IF($b7="","",SUM(COUNTIF($d7:$j7,1)*$j$33)+SUM(COUNTIF($d7:$j7,2)*$j$34)+SUM(COUNTIF($d7:$j7,3)*$j$35)+SUM(COUNTIF($d7:$j7,4)*$j$36)+SUM(COUNTIF($d7:$j7,5)*$j$37)+SUM(COUNTIF($d7:$j7,6)*$j$38)+SUM(COUNTIF($d7:$j7,7)*$j$39)+SUM(COUNTIF($d7:$j7,8)*$j$40))))

Which worked great if there was hours entereed for each shift position. The problem is I am not always able to run every shift all of the time and if any of the J33:J40 range is blank then I get a "#VALUE" error.

I tried to modify my formula to a SUM(IF(COUTIF($D7:$J7,1)*$J$33>0,0)) ...etc but that didn't work either. So I've reached the end of what I know how to do/ can figure out on my own

Does anyone have any suggestions, or helpful tips...that will help me accomplish this.

I have attached the file I've been working with for reference

[dredwolf]

See attached

I changed the formula in yellow highlighted area to avoid the #Value error, and adjusted the formatting in the heavy border are to show partial hours (it was rounding the 43.5 to 44)
The formulas are in the heavy bordered area as well:
in S7:

Please Login or Register  to view this content.

Drag down
In T7:

Please Login or Register  to view this content.

drag down

Hope this helps

[Motoxboi26]

Wow amazing thank you so much....looks like I get to research another new formula type as well....always great to learn new stuff

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"