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>

 

[Marcelo Branco]

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

An example

Row 3

	A​	B​	C​	D​	E​
3​	zzz	19:35​	20:36​	FALSE ​	5​

<tbody>
</tbody>

Why 5 in E3?
Because it overlaps with

	A​	B​	C​	D​
12​	zzz​	19:00​	20:00​	FALSE​

<tbody>
</tbody>

	A​	B​	C​	D​
16​	zzz​	19:11​	20:49​	FALSE​

<tbody>
</tbody>

	A​	B​	C​	D​
26​	zzz​	19:39​	20:26​	FALSE​

<tbody>
</tbody>

	A​	B​	C​	D​
32​	zzz	19:17​	20:49​	FALSE​

<tbody>
</tbody>

	A​	B​	C​	D​
42​	zzz​	18:53​	20:40​	FALSE​

<tbody>
</tbody>

M.

 

[yessir]

Thank you. Is there a function that would be able to sum up the numbers in column E but only for when colomn A are the same. Therefor it would know to start counting column E from Zero again when Column A changes. Thanks for your help.

 

[Marcelo Branco]

Try SUMIF

For example
=SUMIF(A:A,"zzz",E:E)

M.

 

[yessir]

I've given it a shot and it seems to only work for zzz but not when Column A has a different Code.

 

[Marcelo Branco]

I replied too fast...maybe,

F2 copied down
=SUMIF(A$2:A2,A2,E$2:E2)

M.

 

[yessir]

Is there a way to omit the numbers until the last cell, so it only returns one value at the bottom.

Column1	Column2	Column3	Column4	Column5	Column6
qqq	6:15	7:15	FALSE	1	1
qqq	18:06	19:06	FALSE	0	1
qqq	21:40	22:44	FALSE	1	2
qqq	11:40	13:05	FALSE	1	3
qqq	14:07	14:57	FALSE	0	3
qqq	7:12	8:50	FALSE	2	5
qqq	9:39	10:52	FALSE	3	8
qqq	17:13	18:00	FALSE	0	8
qqq	8:46	9:45	FALSE	4	12
qqq	20:10	21:10	FALSE	1	13
qqq	9:10	10:25	FALSE	3	16
qqq	19:32	20:35	FALSE	1	17
qqq	21:54	22:47	FALSE	1	18
qqq	9:28	10:34	FALSE	3	21
qqq	12:37	13:17	FALSE	1	22
qqq	16:22	17:05	FALSE	0	22

<tbody>
</tbody>

For example, we would only see the 22 in the last row an not the numbers above it.

 

[Twollaston]

Thank you. Is there a function that would be able to sum up the numbers in column E but only for when colomn A are the same. Therefor it would know to start counting column E from Zero again when Column A changes. Thanks for your help.

You can create a pivot table, where the row label is the information from column A and the value is the sum of column E

 

[Marcelo Branco]

Try

=IF(A2=A3,"",SUMIF(A:A,A2,E:E))

M.

 

[Twollaston]

removed

 

[yessir]

that formula works. Thanks.

For a different format of the data that im working on i've been using this formula =SUMPRODUCT((B2<=$C$2:$C$45)*(C2>=$B$2:$B$45))>1 I used this formula to simply return a TRUE or FALSE Statement. Where the TRUE value indicates if it overlaps with any other cars in the lot. I was wondering if you could help me figure out how to edit this formula where when column A changes the formula knows to restart and look for overlaps in times at a different location (similar to what we did eailer). Below is the data where Column D represents if it is there overnight and Column E represents if it overlaps with another time.

Arvl Sta A	Arvl Time B	Dept Time C	Is Overnight D	Does Ovlap E
zzz	21:47	8:43	TRUE	FALSE
zzz	19:35	20:36	FALSE	TRUE
zzz	11:01	11:56	FALSE	TRUE
zzz	14:29	15:24	FALSE	TRUE
zzz	17:15	18:04	FALSE	TRUE
zzz	21:00	22:14	FALSE	TRUE
zzz	12:35	13:20	FALSE	TRUE
zzz	15:23	16:10	FALSE	TRUE
zzz	6:44	7:34	FALSE	TRUE
zzz	13:30	14:15	FALSE	TRUE
zzz	19:00	20:00	FALSE	TRUE
zzz	16:17	16:57	FALSE	TRUE
zzz	7:38	8:45	FALSE	TRUE
zzz	11:36	12:34	FALSE	TRUE
zzz	19:11	20:49	FALSE	TRUE
zzz	9:17	10:19	FALSE	TRUE
zzz	16:04	16:59	FALSE	TRUE
zzz	17:41	18:52	FALSE	TRUE
zzz	6:11	7:09	FALSE	TRUE
zzz	14:50	15:40	FALSE	TRUE
zzz	14:47	15:42	FALSE	TRUE
zzz	20:50	22:25	FALSE	TRUE
zzz	13:49	14:34	FALSE	TRUE
zzz	16:43	17:29	FALSE	TRUE
zzz	19:39	20:26	FALSE	TRUE
zzz	11:59	13:00	FALSE	TRUE
zzz	9:00	9:40	FALSE	TRUE
zzz	11:30	12:50	FALSE	TRUE
zzz	8:57	9:37	FALSE	TRUE
zzz	22:25	6:30	TRUE	FALSE
zzz	19:17	20:49	FALSE	TRUE
zzz	20:44	21:46	FALSE	TRUE
zzz	15:54	17:08	FALSE	TRUE
zzz	14:50	15:52	FALSE	TRUE
zzz	14:20	15:30	FALSE	TRUE
zzz	18:13	19:00	FALSE	TRUE
zzz	15:44	17:00	FALSE	TRUE
zzz	16:59	18:15	FALSE	TRUE
zzz	11:23	12:30	FALSE	TRUE
zzz	10:00	11:05	FALSE	TRUE
zzz	18:53	20:40	FALSE	TRUE
zzz	19:53	9:00	TRUE	FALSE
zzz	8:45	10:10	FALSE	TRUE
zzz	12:10	13:25	FALSE	TRUE

<tbody>
</tbody>