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>

 

[Twollaston]

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.

How many different values are there for column A

 

[yessir]

In total there are 336 different values in column A

 

[Twollaston]

In total there are 336 different values in column A

Sorry that is not something I know how to do

 

[yessir]

How many different values are there for column A

There repeating values as you can see in the table I posted, the repeating values just shows that they are in the same lot. Which means there are 336 different locations...Thanks for your help.

 

[Marcelo Branco]

I'm not sure how you want the results. The formula below indicates the number of overlaps for each row.

	A​	B​	C​	D​	E​
1 ​	Arvl Place​	Arvl Time​	Dept Time​	Is Overnight​	Num of Overlaps​
2 ​	xxx​	08:21​	09:19​	FALSE​	0​
3 ​	xxx​	11:08​	11:45​	FALSE​	0​
4 ​	xxx​	14:29​	15:15​	FALSE​	0​
5​	xxx​	15:25​	16:10​	FALSE​	0​
6 ​	xxx​	19:55​	21:20​	FALSE​	1​
7 ​	xxx​	21:10​	22:15​	FALSE​	1​
8 ​	yyy​	21:32​	22:00​	FALSE​	0​
9 ​	yyy​	17:33​	20:00​	FALSE​	3​
10 ​	yyy​	19:42​	20:28​	FALSE​	2​
11​	yyy	19:40​	20:37​	FALSE​	2​
12​	yyy​	14:41​	15:16​	FALSE​	0​
13 ​	yyy​	17:49​	18:46​	FALSE​	1​
14 ​	yyy​	07:50​	10:50​	FALSE​	0​
15 ​	yyy​	15:55​	16:40​	FALSE​	0​
16 ​

<tbody>
</tbody>

Formula in E2 copied down
=SUMPRODUCT(--(ROW(A$2:A$400)<>ROW(A2)),--(A$2:A$400=A2),--(A$2:A$400<>""),--(D$2:D$400=FALSE),1-((B2>C$2:C$400)+(B$2:B$400>C2)))

Hope this helps

M.

 

[yessir]

I'm not sure how you want the results. The formula below indicates the number of overlaps for each row.

Formula in E2 copied down
=SUMPRODUCT(--(ROW(A$2:A$400)<>ROW(A2)),--(A$2:A$400=A2),--(A$2:A$400<>""),--(D$2:D$400=FALSE),1-((B2>C$2:C$400)+(B$2:B$400>C2)))

Hope this helps

M.

This does help for the most part, However when it runs into a row where the D colomn is TRUE it returns a negative number instead of disregarding it's data.

 

[Marcelo Branco]

This does help for the most part, However when it runs into a row where the D colomn is TRUE it returns a negative number instead of disregarding it's data.

Could you show an example where this occurs?

M.

 

[yessir]

Could you show an example where this occurs?

M.

Column1 A	Column2 B	Column3 C	Column4 D	Column5 E
zzz	21:47	8:43	TRUE	-36
zzz	19:35	20:36	FALSE	5
zzz	11:01	11:56	FALSE	4
zzz	14:29	15:24	FALSE	6
zzz	17:15	18:04	FALSE	3
zzz	21:00	22:14	FALSE	2
zzz	12:35	13:20	FALSE	3
zzz	15:23	16:10	FALSE	8
zzz	6:44	7:34	FALSE	1
zzz	13:30	14:15	FALSE	1
zzz	19:00	20:00	FALSE	6
zzz	16:17	16:57	FALSE	4
zzz	7:38	8:45	FALSE	1
zzz	11:36	12:34	FALSE	5
zzz	19:11	20:49	FALSE	6
zzz	9:17	10:19	FALSE	4
zzz	16:04	16:59	FALSE	6
zzz	17:41	18:52	FALSE	3
zzz	6:11	7:09	FALSE	1
zzz	14:50	15:40	FALSE	5
zzz	14:47	15:42	FALSE	5
zzz	20:50	22:25	FALSE	2
zzz	13:49	14:34	FALSE	3
zzz	16:43	17:29	FALSE	6
zzz	19:39	20:26	FALSE	5
zzz	11:59	13:00	FALSE	5
zzz	9:00	9:40	FALSE	3
zzz	11:30	12:50	FALSE	6
zzz	8:57	9:37	FALSE	3
zzz	22:25	6:30	TRUE	-39
zzz	19:17	20:49	FALSE	6
zzz	20:44	21:46	FALSE	4
zzz	15:54	17:08	FALSE	6
zzz	14:50	15:52	FALSE	6
zzz	14:20	15:30	FALSE	6
zzz	18:13	19:00	FALSE	4
zzz	15:44	17:00	FALSE	7
zzz	16:59	18:15	FALSE	7
zzz	11:23	12:30	FALSE	5
zzz	10:00	11:05	FALSE	3
zzz	18:53	20:40	FALSE	6
zzz	19:53	9:00	TRUE	-26
zzz	8:45	10:10	FALSE	5
zzz	12:10	13:25	FALSE	5

<tbody>
</tbody>

 

[Marcelo Branco]

Try this

E2 copied down
=IF(D2=FALSE,SUMPRODUCT(--(ROW(A$2:A$400)<>ROW(A2)),--(A$2:A$400=A2),--(A$2:A$400<>""),--(D$2:D$400=FALSE),1-((B2>C$2:C$400)+(B$2:B$400>C2))),0)

M.

 

[yessir]

The negative numbers are gone, thanks. However, I don't quite understand the formula and what exactly the numbers that they calculate mean. Because the numbers seem to be much higher than the actual number of cars overlapping