I'm going to try to be as descriptive as I can, since I couldn't come up with a title that really explains my issue.
I have an employee schedule that includes a column at the end of each week that gives the total hours worked by each employee for the week. The schedule uses shift codes rather than actual times.
example 2.jpg
Currently I'm using COUNTIF together with SUM in column G - =SUM(COUNTIF(B4:E4,{"OBA","OSA","NOMA","C","FL","SS1"})*{6,6,5,5,6,8})
However, we now have schedules that vary daily, depending on the number of movements we have. On 3 movement days SS1=8, C=7, OBA=8, etc. On 2 movement days SS1=8, C=5, OBA=6, etc. I need to somehow add a function that uses the criteria in row 1 (MOVEMENTS) to determine what the value of the shift code is on that day, and then add the total weekly hours as it currently does. I can't change the format, so adding columns or rows isn't an option. I have thought about adding a worksheet that defines the values of each shift, but haven't been able to make that work either.
Perhaps I'm over complicating it in my head and there's a very easy solution to this one.
Thanks!
Bookmarks