Counting occurances in the same time range

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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?

Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
=SUMPRODUCT(--($A$2:$A$200=A2),--(B2>=$B$2:$B$200).--(C2<$C$2@$C$2--))
Adjust ranges to fit
 
Upvote 0

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Thanks for replying

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

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

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

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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.
 
Upvote 0

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
Did you try the formula with double hyphen's? The link is showing how to filter data using SUMPRODUCT with specific criteria
 
Upvote 0

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
I did thanks,

I currently have this, which is very nearly there

Code:
=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
 
Upvote 0

Forum statistics

Threads
1,186,516
Messages
5,958,300
Members
438,348
Latest member
JS050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top