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]

 

[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
?


[TABLE="width: 650"]
<tbody>[TR]
[TD]Arvl Sta[/TD]
[TD]Arvl Time[/TD]
[TD]Dept Time[/TD]
[TD]Is Overnight[/TD]
[TD]Does Ovlap[/TD]
[TD]Count Overlap[/TD]
[TD]Count Overnight unless Overlapped[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]21:47[/TD]
[TD="align: right"]8:43[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]19:35[/TD]
[TD="align: right"]20:36[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]11:01[/TD]
[TD="align: right"]11:56[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]14:29[/TD]
[TD="align: right"]15:24[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]18:04[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]22:14[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]12:35[/TD]
[TD="align: right"]13:20[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]15:23[/TD]
[TD="align: right"]16:10[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]6:44[/TD]
[TD="align: right"]7:34[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]13:30[/TD]
[TD="align: right"]14:15[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]20:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]16:17[/TD]
[TD="align: right"]16:57[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]7:38[/TD]
[TD="align: right"]8:45[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]11:36[/TD]
[TD="align: right"]12:34[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]19:11[/TD]
[TD="align: right"]20:49[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]9:17[/TD]
[TD="align: right"]10:19[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]16:04[/TD]
[TD="align: right"]16:59[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]17:41[/TD]
[TD="align: right"]18:52[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]6:11[/TD]
[TD="align: right"]7:09[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]14:50[/TD]
[TD="align: right"]15:40[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]14:47[/TD]
[TD="align: right"]15:42[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]20:50[/TD]
[TD="align: right"]22:25[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]13:49[/TD]
[TD="align: right"]14:34[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]16:43[/TD]
[TD="align: right"]17:29[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]19:39[/TD]
[TD="align: right"]20:26[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]11:59[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]9:40[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]11:30[/TD]
[TD="align: right"]12:50[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]8:57[/TD]
[TD="align: right"]9:37[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]22:25[/TD]
[TD="align: right"]6:30[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]19:17[/TD]
[TD="align: right"]20:49[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]20:44[/TD]
[TD="align: right"]21:46[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]15:54[/TD]
[TD="align: right"]17:08[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]14:50[/TD]
[TD="align: right"]15:52[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]14:20[/TD]
[TD="align: right"]15:30[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]18:13[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]15:44[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]16:59[/TD]
[TD="align: right"]18:15[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]11:23[/TD]
[TD="align: right"]12:30[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]11:05[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]18:53[/TD]
[TD="align: right"]20:40[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]19:53[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]8:45[/TD]
[TD="align: right"]10:10[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD="align: right"]12:10[/TD]
[TD="align: right"]13:25[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[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