count workers overlappings without counting duplicates - formula

[Osvaldo Palmeiro]

Viaturas For Simult.xlsm
	A	B	C	D	E	F
1	workers	start time	end time	expected results unique workers in each interval	visual check of expected results A2=worker A in row 2 B3=worker B in row 3 and so on ...
2	A	10:00:00	20:20:00	6	A2, B3, C4, F5, D6, E7
3	B	10:05:00	10:45:00	5	A2, B3, C4, F5, D6
4	C	10:10:00	10:45:00	5	A2, B3, C4, F5, D6
5	F	10:30:00	11:00:00	6	A2, B3, C4, F5, D6, E7
6	D	10:35:00	11:25:00	6	A2, B3, C4, F5, D6, E7
7	E	10:45:00	13:00:00	6	A2, F5, D6, E7, B8, C9
8	B	11:00:00	11:30:00	4	A2, D6, E7, B8
9	C	12:00:00	14:00:00	5	A2, E7, C9, F10, D11
10	F	12:30:00	14:00:00	5	A2, E7, C9, F10, D11
11	D	13:00:00	13:10:00	4	A2, C9, F10, D11
12	E	13:30:00	15:00:00	4	A2, C9, F10, E12
13	F	15:00:00	18:00:00	5	A2, F13, B14, C15, D16
14	B	15:50:00	16:00:00	3	A2, F13, B14	as an example, highlighted
15	C	17:00:00	17:15:00	3	A2, F13, C15	<<< this one to clarify
16	D	17:30:00	18:00:00	3	A2, F13, D16
17	A	21:05:00	22:10:00	1	A17
MrExcel

Hi all.
=SUM(--(FREQUENCY(IF((C$2:C$17>B2)*(C$2:C$17<C2),MATCH(A$2:A$17,A$2:A$17,0)),ROW(A$2:A$17)-1)>0))
I am using this formula with 4 variations at the IF condition in order to count overlapping of workers in column A, if they are simultaneously working accordingly to each hour interval in columns B and C (B=start time and C=end time of work).

Several workers enter and leave the working room several times during the day, so I am using the FREQUENCY() function to count overlapping of each worker only once in each interval.

The problem I am facing is some of overlappingS are being counted twice in some of these 4 variations, so I think if I could join the 4 conditions in just one formula it would work fine.

In plain expression, after adding one more condition to the formula above, I think it would be something like:
... FREQUENCY(OR(IF((C$2:C$17>B2)*(C$2:C$17<C2), IF((B$2:B$17>B2)*(B$2:B$17<C2), MATCH(...
I've tried some variations of this but with no success.

I don't know if this is possible. So, if it's possible and someone could please add the second OR condition like I showed in plain expression, then I think I'll be able to add the others two OR conditions.

If there is another way to do this job then I'm open and would appreciate.

Thanks so much for any help.

 

[felixstraube]

How about this?:

worker overlapping.xlsx
	A	B	C	D	E	F
1	workers	start time	end time	expected results unique workers in each interval	visual check of expected results A2=worker A in row 2 B3=worker B in row 3 and so on ...	Formula
2	A	10:00:00	20:20:00	6	A2, B3, C4, F5, D6, E7	6
3	B	10:05:00	10:45:00	5	A2, B3, C4, F5, D6	5
4	C	10:10:00	10:45:00	5	A2, B3, C4, F5, D6	5
5	F	10:30:00	11:00:00	6	A2, B3, C4, F5, D6, E7	6
6	D	10:35:00	11:25:00	6	A2, B3, C4, F5, D6, E7	6
7	E	10:45:00	13:00:00	6	A2, F5, D6, E7, B8, C9	6
8	B	11:00:00	11:30:00	4	A2, D6, E7, B8	4
9	C	12:00:00	14:00:00	5	A2, E7, C9, F10, D11	5
10	F	12:30:00	14:00:00	5	A2, E7, C9, F10, D11	5
11	D	13:00:00	13:10:00	4	A2, C9, F10, D11	4
12	E	13:30:00	15:00:00	4	A2, C9, F10, E12	4
13	F	15:00:00	18:00:00	5	A2, F13, B14, C15, D16	5
14	B	15:50:00	16:00:00	3	A2, F13, B14	3
15	C	17:00:00	17:15:00	3	A2, F13, C15	3
16	D	17:30:00	18:00:00	3	A2, F13, D16	3
17	A	21:05:00	22:10:00	1	A17	1
Hoja1

Cell Formulas
Range	Formula
F2:F17	F2	=SUM((UNIQUE(IFERROR(FILTER($A$2:$A$17,($A$2:$A$17<>A2)*($B$2:$B$17<C2)*($C$2:$C$17>B2)),""))<>"")*1)+1

 

[Osvaldo Palmeiro]

Hi, @felixstraube .
Thanks a lot, that works fine.

But, my fault, I forgot to say that the solution should be for XL 2013.
I know my profile says 365 version, but this file I'm working on is to be used for others in XL 2013.
I'm sorry for my fault.

Could you, please, think a solution for that version?

Many thanks again.

 

[felixstraube]

How about this formula in F2 and drag down?:
I think you already know that it is an array formula and you have to enter it with Ctrl+Shift+Enter.

=SUM((FREQUENCY(IFERROR(XMATCH(IF((A2<>$A$2:$A$17)*(B2<$C$2:$C$17)*(C2>$B$2:$B$17),$A$2:$A$17,""),$A$2:$A$17,0),""),IFERROR(XMATCH(IF((A2<>$A$2:$A$17)*(B2<$C$2:$C$17)*(C2>$B$2:$B$17),$A$2:$A$17,""),$A$2:$A$17,0),""))>0)*1)+1

 

[Osvaldo Palmeiro]

Hi, @felixstraube.

Thank you very very much.

I changed XMATCH(() to MATCH() and it seems to work nicely for all start/end time variations, including in XL 2013.

It's a Xmas gift.

Thanks again.

 

[felixstraube]

Sure, sorry i forgot XMATCH is one of the new functions. Happy to help. Thanks for the feedback.