Formula Help with overlapping times

[yessir]

I am trying to find a formula that would help me find how many cars overlap and are in the lot at the same time but only when column D is False. I would then want to count how many cars are in the lot at the same time. The start times are located in B2:B7 and end times are in C2:C7. I've tried using a SUMPRODUCT function but was wondering if there was an easier way to go about this and include the function to count the number of cars that overlap.
A B C D
[TABLE="width: 567"]
<tbody>[TR]
[TD]Arvl Lot Location[/TD]
[TD] Arvl Time[/TD]
[TD] Dept Time[/TD]
[TD]Is Overnight[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]15:25[/TD]
[TD]16:10[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]21:10[/TD]
[TD]22:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]8:21[/TD]
[TD]9:19[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]11:08[/TD]
[TD]11:45[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]14:29[/TD]
[TD]15:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]19:55[/TD]
[TD]21:20[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, welcome to the board.

Help us out with your data please.
With the sample data provided, what should the results be, and why ?

 

[yessir]

The result should be a count of the overlapping cars that are in the lot that do not stay overnight. The reason for this is because I need to know how many cars are in the lot at overlapping times.

 

[yessir]

Does that make sense? : / apologies if i'm not explaning it properly.

 

[Twollaston]

I am trying to find a formula that would help me find how many cars overlap and are in the lot at the same time but only when column D is False. I would then want to count how many cars are in the lot at the same time. The start times are located in B2:B7 and end times are in C2:C7. I've tried using a SUMPRODUCT function but was wondering if there was an easier way to go about this and include the function to count the number of cars that overlap.
A B C D
[TABLE="width: 567"]
<tbody>[TR]
[TD]Arvl Lot Location[/TD]
[TD] Arvl Time[/TD]
[TD] Dept Time[/TD]
[TD]Is Overnight[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]15:25[/TD]
[TD]16:10[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]21:10[/TD]
[TD]22:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]8:21[/TD]
[TD]9:19[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]11:08[/TD]
[TD]11:45[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]14:29[/TD]
[TD]15:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]19:55[/TD]
[TD]21:20[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

Try this formula
=IF(D2=FALSE,SUMPRODUCT((B2<$C$2:$C$7)*(C2>=$B$2:$B$7))>1,"")

paste this in row 2 in any column and drag it down to all your data
please note $C$2:$C$7 you should be putting your entire dept time range, so if you have 100 dates then it would be $C$2:$C$102, just basically grab your entire range of dept time, and click f4 to lock the reference, and then do the same thing where it says $B$2:$B$7 but instead of end dates put arvl time range

 

[Gerald Higgins]

Yeah so what is the answer with that sample data ?
1?
2?
3?

I THINK rows 2 and 6 overlap on time, if I am reading this correctly.
If that's right, what should the answer be ?
2 ?

 

[Twollaston]

Yeah so what is the answer with that sample data ?
1?
2?
3?

I THINK rows 2 and 6 overlap on time, if I am reading this correctly.
If that's right, what should the answer be ?
2 ?

Yes good point he did not specify what he wants it to return if the condition is true or not.
My results looked like this
[TABLE="width: 393"]
<tbody>[TR]
[TD]Arvl Lot Location[/TD]
[TD]Arvl Time[/TD]
[TD]Dept Time[/TD]
[TD]Is Overnight[/TD]
[TD]Overlap[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]15:25[/TD]
[TD="align: right"]16:10[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]21:10[/TD]
[TD="align: right"]22:15[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]8:21[/TD]
[TD="align: right"]9:19[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]11:08[/TD]
[TD="align: right"]11:45[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]14:29[/TD]
[TD="align: right"]15:15[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]19:55[/TD]
[TD="align: right"]21:20[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]


I think he said he wants the count, so he can use this formula instead
=IF(D2=FALSE,if(SUMPRODUCT((B2<$C$2:$C$7)*(C2>=$B$2:$B$7))>1,1,0),0)
and it will give him the count

[TABLE="width: 393"]
<tbody>[TR]
[TD]Arvl Lot Location[/TD]
[TD]Arvl Time[/TD]
[TD]Dept Time[/TD]
[TD]Is Overnight[/TD]
[TD]Overlap[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]15:25[/TD]
[TD="align: right"]16:10[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]21:10[/TD]
[TD="align: right"]22:15[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]8:21[/TD]
[TD="align: right"]9:19[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]11:08[/TD]
[TD="align: right"]11:45[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]14:29[/TD]
[TD="align: right"]15:15[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: right"]19:55[/TD]
[TD="align: right"]21:20[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[yessir]

You are correct the Answer should be 2. Is there a way to write a formula that would then be able to count the Number of times the True value was returned from the formula that you gave me.

 

[Twollaston]

You are correct the Answer should be 2. Is there a way to write a formula that would then be able to count the Number of times the True value was returned from the formula that you gave me.

=IF(D2=FALSE,if(SUMPRODUCT((B2<$C$2:$C$7)*(C2>=$B$2:$B$7))>1,1,0),0)
Use this

and then sum the entire column in a cell, so if you are posting this formula to E column
Then in F1 or anywhere you want outside the data type =SUM(E:E)

 

[yessir]

Thanks so much for your help. The formula worked great. However, I do have a follow up question.
A B C D E
[TABLE="width: 271"]
<tbody>[TR]
[TD="width: 71, bgcolor: transparent"]Arvl Place[/TD]
[TD="width: 69, bgcolor: transparent"]Arvl Time[/TD]
[TD="width: 74, bgcolor: transparent"]Dept Time[/TD]
[TD="width: 82, bgcolor: transparent"]Is Overnight[/TD]
[TD="width: 64, bgcolor: transparent"]Overlap[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]8:21[/TD]
[TD="bgcolor: transparent"]9:19[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]11:08[/TD]
[TD="bgcolor: transparent"]11:45[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]14:29[/TD]
[TD="bgcolor: transparent"]15:15[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]15:25[/TD]
[TD="bgcolor: transparent"]16:10[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]19:55[/TD]
[TD="bgcolor: transparent"]21:20[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]21:10[/TD]
[TD="bgcolor: transparent"]22:15[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]21:32[/TD]
[TD="bgcolor: transparent"]22:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]17:33[/TD]
[TD="bgcolor: transparent"]20:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]19:42[/TD]
[TD="bgcolor: transparent"]20:28[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]19:40[/TD]
[TD="bgcolor: transparent"]20:37[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]14:41[/TD]
[TD="bgcolor: transparent"]15:16[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]17:49[/TD]
[TD="bgcolor: transparent"]18:46[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]7:50[/TD]
[TD="bgcolor: transparent"]10:50[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]15:55[/TD]
[TD="bgcolor: transparent"]16:40[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to write ino the formula for it to be able to distingush the change in coloum A? Meaning the formula would only check overlap times when coloum A is the same? Thanks.