Hi,

I have a weekly work roster and i'm trying to count the number of instances each staff member has worked passed 6pm during the week. I need a function that would count only if the time is greater than 6pm and the criteria for the employee name is met.

example of where the data is in the cells

B4 & C4 (merged cells) is employees name (drop down list)
B5 is start time e.g. 09:30 hours
C5 is finish time e.g. 18:30 hours
D5 is Meal break length time e.g. 01:00 hour
D4 is formula to calculate total hours worked in the shift. (I'm using a SUMIF function elsewhere to sum the total weekly hors for each employee)

I thought COUNTIFS function with criteria 1 being “>18:00” and criteria 2 being the “employees name” . I can’t get this work though as the employees name is in a cell above and to the left of the cell containing the first criteria. I then thought I could maybe try OFFSET but can’t get this to work.

My excel skills are very limited so I would really appreciate help on this.