CountIFs for Time Range with Multiple Start End Times

[Sloppyjo3]

Hi folks,

I am trying to do a count on people entering in the system outside of their work hours. They all start and end and different times, for example

Employee A: 7:00-4:15
Employee B: 6:30-3:45
Employee C: 8:00-5:15

My data looks like the below....is there a CountIFs I can do that will give me a count of each time outside of that particular employee's range?

6:57:27 AM​	Employee A
6:59:40 AM​	Employee A
3:00:48 AM​	Employee B
7:01:35 AM​	Employee C
5:01:37 PM​	Employee C
7:01:40 AM​	Employee B
4:02:56 AM​	Employee A

 

[maabadi]

Try this:
if you want only Before start time only use Part 1 of formula and if also want Exit after End time, use total formula.

Mail Generator2.xlsm
	A	B	C	D	E	F	G	H
1	Time	Employee		Employee	Start	END	Countifs
2	6:57:27 AM	Employee A		Employee A	7:00:00 AM	4:15:00 PM	3
3	6:59:40 AM	Employee A		Employee B	6:45:00 AM	3:45:00 PM	1
4	3:00:48 AM	Employee B		Employee C	8:00:00 AM	5:15:00 PM	1
5	7:01:35 AM	Employee C
6	5:01:37 PM	Employee C
7	7:01:40 AM	Employee B
8	4:02:56 AM	Employee A
PL2001

Cell Formulas
Range	Formula
G2:G4	G2	=COUNTIFS($B$2:$B$8,D2,$A$2:$A$8,"<" & E2)+ COUNTIFS($B$2:$B$8,D2,$A$2:$A$8,">" & F2)

Named Ranges
Name	Refers To	Cells
'PL2001'!Extract	='PL2001'!$A$3:$A$3	G2:G4

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Please be sure to follow & read the link at the end of the rule too!

Cross posted at: COUNTIFS for Time Range with multiple start/end times
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Sloppyjo3]

That is great, and now that I see it - I am wondering how to identify on the line itself that it is or is not within the specified time range? Something like

 

[maabadi]

You should use Index Table at column E:G Then

Mail Generator2.xlsm
	A	B	C	D	E	F	G	H	I
1	Time	Employee	Outside Range		Employee	Start	END	Countifs
2	6:57:27 AM	Employee A	Yes		Employee A	7:00:00 AM	4:15:00 PM	3
3	6:59:40 AM	Employee A	Yes		Employee B	6:45:00 AM	3:45:00 PM	1
4	3:00:48 AM	Employee B	Yes		Employee C	8:00:00 AM	5:15:00 PM	1
5	7:01:35 AM	Employee C	Yes
6	5:01:37 PM	Employee C	NO
7	7:01:40 AM	Employee B	NO
8	4:02:56 AM	Employee A	Yes
9
PL2001

Cell Formulas
Range	Formula
H2:H4	H2	=COUNTIFS($B$2:$B$8,E2,$A$2:$A$8,"<" & F2)+ COUNTIFS($B$2:$B$8,E2,$A$2:$A$8,">" & G2)
C2:C8	C2	=IF(OR(A2<VLOOKUP(B2,$E$1:$G$4,2,FALSE),A2>VLOOKUP(B2,$E$1:$G$4,3,FALSE)),"Yes","NO")

Named Ranges
Name	Refers To	Cells
'PL2001'!Extract	='PL2001'!$A$3:$A$3	C3, H2:H4