# Calculating hours based on data validation selection

Hello all!

I was wondering if someone could tell me the best way to have the hours calculated in the "May" tab translate into the Employee View Tab under the respective employee? I'm trying to get it so when I select an employees name in a cell under the "Shift View" tab it calculates the hours for that employee on that day. So ostensibly I could have an employee work two shifts in a day, and it would automatically sum that up.

I have been googling all night and I can't seem to find anything I can wrap my head around.

• Revised sheet here May 18, 2020 at 10:03

If I understand correctly, you want to calculate the total hours for each employee in a day (May Schedule) and populate to (Employee View). To decide whether to count the employee by looking whether it exists in (Shift View) on that particular day.

``````=IF(ISNUMBER(MATCH(H\$1,'Shift View'!\$E6:\$H6)), COUNTIF(May!\$E9:\$L9,H\$1),0)
``````

`MATCH(H\$1,'Shift View'!\$E6:\$H6)` find if employee exists in shift view and return its position. Return error if not found

`ISNUMBER(...)` validate the value is a number

`COUNTIF(May!\$E9:\$L9,H\$1)` count only if employee found in the may schedule

To wrap them up, `IF(ISNUMBER(MATCH(H\$1,'Shift View'!\$E6:\$H6)), COUNTIF(May!\$E9:\$L9,H\$1),0)` count the employee in may schedule if it is found in schedule view else return 0.

Revised Sheet

• Not quite, but this is on the right track! I need it to see the rest of the shifts, and I was hoping it could pull the number at the right of each block, that way if a shift is less than 8 hours (say 7.5) I can update it in the Monthly Schedule and have it update the rest of the board. I did some tidying up I should have done with the first example here. I was playing around with ways I could remove the hidden columns from the formula entirely, but I ran out of time to work on it. May 18, 2020 at 9:59
• I am guessing again, after i read your revision sheet, now you want your "Employee View to show the allocated hours based on their name assigned in the "Schedule View". Part I couldn't get it is your employee in "may schedule" is always 8 hours, in what cases it will be 7.5. In such cases , you may just check if the employee exists in "Schedule View" and assigned 8 hours under their name in "Employee View" May 18, 2020 at 13:34
• Correct. I'm thinking I need to us SUMIF, remove the colums with each hour and just have the column for total hours set to whatever the shift's hourly total is. May 19, 2020 at 5:51

Use COUNTIF

For example, put the following code in `'Employee View'!E3` and drag the fill handle to fill all blank cells: `=COUNTIF('Shift View'!\$E3:\$H3, E\$1)`

If you need to translate shifts to hours, multiply the expression above by the number of hours per shift.

• Like the above suggestion, this is on the right track. The problem is that I'd like it to calculate the hours at the monthly schedule tab, since some shifts will be different lengths than others. any suggestions on how to get through that? May 18, 2020 at 10:04