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

Arvl Lot Location	Arvl Time	Dept Time	Is Overnight
xxx	15:25	16:10	FALSE
xxx	21:10	22:15	FALSE
xxx	8:21	9:19	FALSE
xxx	11:08	11:45	FALSE
xxx	14:29	15:15	FALSE
xxx	19:55	21:20	FALSE

<tbody>
</tbody>

 

[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

Arvl Lot Location	Arvl Time	Dept Time	Is Overnight
xxx	15:25	16:10	FALSE
xxx	21:10	22:15	FALSE
xxx	8:21	9:19	FALSE
xxx	11:08	11:45	FALSE
xxx	14:29	15:15	FALSE
xxx	19:55	21:20	FALSE

<tbody>
</tbody>

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

Arvl Lot Location	Arvl Time	Dept Time	Is Overnight	Overlap
xxx	15:25	16:10	FALSE	FALSE
xxx	21:10	22:15	FALSE	TRUE
xxx	8:21	9:19	FALSE	FALSE
xxx	11:08	11:45	FALSE	FALSE
xxx	14:29	15:15	FALSE	FALSE
xxx	19:55	21:20	FALSE	TRUE

<tbody>
</tbody>

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

Arvl Lot Location	Arvl Time	Dept Time	Is Overnight	Overlap
xxx	15:25	16:10	FALSE	0
xxx	21:10	22:15	FALSE	1
xxx	8:21	9:19	FALSE	0
xxx	11:08	11:45	FALSE	0
xxx	14:29	15:15	FALSE	0
xxx	19:55	21:20	FALSE	1

<tbody>
</tbody>

 

[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

Arvl Place	Arvl Time	Dept Time	Is Overnight	Overlap
xxx	8:21	9:19	FALSE	0
xxx	11:08	11:45	FALSE	0
xxx	14:29	15:15	FALSE	0
xxx	15:25	16:10	FALSE	0
xxx	19:55	21:20	FALSE	1
xxx	21:10	22:15	FALSE	1
yyy	21:32	22:00	FALSE
yyy	17:33	20:00	FALSE
yyy	19:42	20:28	FALSE
yyy	19:40	20:37	FALSE
yyy	14:41	15:16	FALSE
yyy	17:49	18:46	FALSE
yyy	7:50	10:50	FALSE
yyy	15:55	16:40	FALSE

<tbody>
</tbody>

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.