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]

So you want the same formula he made before except you want it to say true or false instead of the number of overlaps?

=IF(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)>0,TRUE,FALSE)

 

[yessir]

So you want the same formula he made before except you want it to say true or false instead of the number of overlaps?

=IF(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)>0,TRUE,FALSE)

This works for most of my data. Is there a SUMIF function I can use that would add up the total number of times TRUE appears from the function we just used. This new function would be very similar to the SUMIF function used when counting the numbers that we had used before. This new function would need to be able to distingush when Column A changes and only puts out the find SUM number on the last row before Column A changes.

 

[Marcelo Branco]

This works for most of my data. Is there a SUMIF function I can use that would add up the total number of times TRUE appears from the function we just used. This new function would be very similar to the SUMIF function used when counting the numbers that we had used before. This new function would need to be able to distingush when Column A changes and only puts out the find SUM number on the last row before Column A changes.

Have you seen the formula in post 28?

M.

 

[Marcelo Branco]

Sorry, i misread your post

Try in F2 copied down
=IF(A2<>A3,SUMPRODUCT(--(A$2:A$500=A2),--E$2:E$500),"")

M.

 

[yessir]

Sorry, i misread your post

Try in F2 copied down
=IF(A2<>A3,SUMPRODUCT(--(A$2:A$500=A2),--E$2:E$500),"")

M.

The purpose of this function would be to SUM the total TRUE statements in D, but only when E reads FALSE. I copied the above fourmula and it returned #VALUE !.

 

[Twollaston]

The purpose of this function would be to SUM the total TRUE statements in D, but only when E reads FALSE. I copied the above fourmula and it returned #VALUE !.

Oh i misread that, let me recalculate

 

[Marcelo Branco]

The purpose of this function would be to SUM the total TRUE statements in D, but only when E reads FALSE. I copied the above fourmula and it returned #VALUE !.

Do you want to sum (count) the TRUE statements in column D or in column E? I'm confused.

M.

 

[Twollaston]

the purpose of this function would be to sum the total true statements in d, but only when e reads false. I copied the above fourmula and it returned #value !.

Column E
=IF(IF(D45=FALSE,SUMPRODUCT(--(ROW(A$2:A$400)<>ROW(A45)),--(A$2:A$400=A45),--(A$2:A$400<>""),--(D$2:D$400=FALSE),1-((B45>C$2:C$400)+(B$2:B$400>C45))),0)>0,TRUE,FALSE)

Column F
=IF(A2=A3,"",COUNTIFS(A:A,A2,E:E,TRUE))

Column G
=if(a2=a3,"",countifs(a:a,a2,d:d,true,e:e,false))

Is this what you want to have the count of the overlaps for Arvl Sta in column F
and to have the count of the overnights, when not overlapped for Arvl Sta in column G
?

Arvl Sta	Arvl Time	Dept Time	Is Overnight	Does Ovlap	Count Overlap	Count Overnight unless Overlapped
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	41	3

<tbody>
</tbody>

 

[yessir]

Do you want to sum (count) the TRUE statements in column D or in column E? I'm confused.

M.

I want to SUM (count) the TRUE statements in E but only when the valu along the same row in column D is FALSE. I understand that in the table abv Column D is all false however for some of my other data there are some True statements in Column D.

Sorry for the confusion, appreciate your help.

 

[Twollaston]

I want to SUM (count) the TRUE statements in E but only when the valu along the same row in column D is FALSE. I understand that in the table abv Column D is all false however for some of my other data there are some True statements in Column D.

Sorry for the confusion, appreciate your help.

Check the 3 formulas in my previous post, and tell me if that's waht you want.

Also, there is already a condition in column E for not including falses: D2=FALSE in your long formula so there won't be a situation where E says true and D says true so really all you need is a count if for the trues and falses in each column