Counting occurances in the same time range

[Grizlore]

Hi All,

Any help wound be appreciated.

I have four lines, A,B,C & D and the times of when they start and stop. I am attempting to count (in column D) which lines are also on during the same time range.

I have tried counts and sumproduct, but to not avail.

Anyone have a suggestion please?

Line    Start Time                 End Time	              Count of other lines on during this range
C	25/09/2018 11:15:44	   25/09/2018 11:15:58	
D	25/09/2018 11:13:38	   25/09/2018 11:13:52	
D	25/09/2018 11:13:28	   25/09/2018 11:13:34	
D	25/09/2018 11:02:28	   25/09/2018 11:02:36	
D	25/09/2018 10:55:46	   25/09/2018 10:56:02	
D	25/09/2018 10:55:16	   25/09/2018 10:55:38	
D	25/09/2018 10:52:24	   25/09/2018 10:52:40	
B	25/09/2018 10:44:54	   25/09/2018 10:45:00	
A	25/09/2018 10:41:14	   25/09/2018 10:41:18	
A	25/09/2018 10:40:14	   25/09/2018 10:41:12	
A	25/09/2018 10:37:27	   25/09/2018 10:37:31	
A	25/09/2018 10:36:03	   25/09/2018 10:37:25	
D	25/09/2018 10:35:34	   25/09/2018 10:35:55	
B	25/09/2018 10:35:23	   25/09/2018 10:35:45	
D	25/09/2018 10:35:10	   25/09/2018 10:35:24	
B	25/09/2018 10:35:05	   25/09/2018 10:35:09	
B	25/09/2018 10:34:32	   25/09/2018 10:35:03	
D	25/09/2018 10:30:01	   25/09/2018 10:30:05	
D	25/09/2018 10:27:47	   25/09/2018 10:28:07	
D	25/09/2018 10:16:35	   25/09/2018 10:16:55	
D	25/09/2018 10:09:59	   25/09/2018 10:10:13

 

[JackDanIce]

Try:

=SUMPRODUCT(--($A$2:$A$200=A2),--(B2>=$B$2:$B$200).--(C2<$C$2@$C$2--))

Adjust ranges to fit

 

[Grizlore]

Thanks for replying

don't get this part --> --(C2<$C$2@$C$2--))

So changed to - but not counting up the number of reoccurrences

=SUMPRODUCT(--($A$2:$A$200=A2),--(B2>=$B$2:$B$200),--(C2<$C$2:$C$200))

 

[JackDanIce]

Try with the double hypens

This link explains more: https://chandoo.org/wp/excel-sumproduct-formula/

 

[Grizlore]

Had a good look, but cant see how this will compare the timeframes with each other and count if they fall in the same timeframe.

 

[JackDanIce]

Did you try the formula with double hyphen's? The link is showing how to filter data using SUMPRODUCT with specific criteria

 

[Grizlore]

I did thanks,

I currently have this, which is very nearly there

=1+(SUMPRODUCT(--($A$2:$A$200<>A2),--(B2>=$B$2:$B$200),--(B2<$C$2:$C$200)))

Think I need to incorporate some IF statements as they are not all being counted