Formula Help with overlapping times

Y

yessir

Board Regular
Joined
Jun 7, 2019
Messages
103
Office Version
  1. 2021
Platform
  1. MacOS
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]
 
Last edited by a moderator:
Sort by date Sort by votes
yessir said:
Thanks so much for your help. The formula worked great. However, I do have a follow up question.
A B C D E
[TABLE="width: 271"]
<tbody>[TR]
[TD="width: 71, bgcolor: transparent"]Arvl Place[/TD]
[TD="width: 69, bgcolor: transparent"]Arvl Time[/TD]
[TD="width: 74, bgcolor: transparent"]Dept Time[/TD]
[TD="width: 82, bgcolor: transparent"]Is Overnight[/TD]
[TD="width: 64, bgcolor: transparent"]Overlap[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]8:21[/TD]
[TD="bgcolor: transparent"]9:19[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]11:08[/TD]
[TD="bgcolor: transparent"]11:45[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]14:29[/TD]
[TD="bgcolor: transparent"]15:15[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]15:25[/TD]
[TD="bgcolor: transparent"]16:10[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]19:55[/TD]
[TD="bgcolor: transparent"]21:20[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent"]21:10[/TD]
[TD="bgcolor: transparent"]22:15[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]21:32[/TD]
[TD="bgcolor: transparent"]22:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]17:33[/TD]
[TD="bgcolor: transparent"]20:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]19:42[/TD]
[TD="bgcolor: transparent"]20:28[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]19:40[/TD]
[TD="bgcolor: transparent"]20:37[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]14:41[/TD]
[TD="bgcolor: transparent"]15:16[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]17:49[/TD]
[TD="bgcolor: transparent"]18:46[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]7:50[/TD]
[TD="bgcolor: transparent"]10:50[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent"]15:55[/TD]
[TD="bgcolor: transparent"]16:40[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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.
Click to expand...

How many different values are there for column A
 
Last edited by a moderator:
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Twollaston said:
How many different values are there for column A
Click to expand...

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.
 
Upvote 0
I'm not sure how you want the results. The formula below indicates the number of overlaps for each row.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
​
[/TD]
[TD]
Arvl Place​
[/TD]
[TD]
Arvl Time​
[/TD]
[TD]
Dept Time​
[/TD]
[TD]
Is Overnight​
[/TD]
[TD]
Num of Overlaps​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
​
[/TD]
[TD]
xxx​
[/TD]
[TD]
08:21​
[/TD]
[TD]
09:19​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
​
[/TD]
[TD]
xxx​
[/TD]
[TD]
11:08​
[/TD]
[TD]
11:45​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
​
[/TD]
[TD]
xxx​
[/TD]
[TD]
14:29​
[/TD]
[TD]
15:15​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
xxx​
[/TD]
[TD]
15:25​
[/TD]
[TD]
16:10​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
​
[/TD]
[TD]
xxx​
[/TD]
[TD]
19:55​
[/TD]
[TD]
21:20​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
​
[/TD]
[TD]
xxx​
[/TD]
[TD]
21:10​
[/TD]
[TD]
22:15​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8
​
[/TD]
[TD]
yyy​
[/TD]
[TD]
21:32​
[/TD]
[TD]
22:00​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9
​
[/TD]
[TD]
yyy​
[/TD]
[TD]
17:33​
[/TD]
[TD]
20:00​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10
​
[/TD]
[TD]
yyy​
[/TD]
[TD]
19:42​
[/TD]
[TD]
20:28​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]yyy[/TD]
[TD]
19:40​
[/TD]
[TD]
20:37​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
yyy​
[/TD]
[TD]
14:41​
[/TD]
[TD]
15:16​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13
​
[/TD]
[TD]
yyy​
[/TD]
[TD]
17:49​
[/TD]
[TD]
18:46​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14
​
[/TD]
[TD]
yyy​
[/TD]
[TD]
07:50​
[/TD]
[TD]
10:50​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15
​
[/TD]
[TD]
yyy​
[/TD]
[TD]
15:55​
[/TD]
[TD]
16:40​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16
​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Last edited by a moderator:
Upvote 0
Marcelo Branco said:
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.
Click to expand...


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.
 
Last edited by a moderator:
Upvote 0
yessir said:
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.
Click to expand...

Could you show an example where this occurs?

M.
 
Upvote 0
Marcelo Branco said:
Could you show an example where this occurs?

M.
Click to expand...

[TABLE="width: 407"]
<tbody>[TR]
[TD="width: 122"]Column1
A[/TD]
[TD="width: 123"]Column2 B[/TD]
[TD="width: 139"]Column3
C[/TD]
[TD="width: 82"]Column4 D[/TD]
[TD="width: 76"]Column5
E[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]21:47[/TD]
[TD]8:43[/TD]
[TD]TRUE[/TD]
[TD="align: right"]-36[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]19:35[/TD]
[TD="bgcolor: transparent"]20:36[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]11:01[/TD]
[TD]11:56[/TD]
[TD]FALSE[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]14:29[/TD]
[TD="bgcolor: transparent"]15:24[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]17:15[/TD]
[TD]18:04[/TD]
[TD]FALSE[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]21:00[/TD]
[TD="bgcolor: transparent"]22:14[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]12:35[/TD]
[TD]13:20[/TD]
[TD]FALSE[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]15:23[/TD]
[TD="bgcolor: transparent"]16:10[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]6:44[/TD]
[TD]7:34[/TD]
[TD]FALSE[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]13:30[/TD]
[TD="bgcolor: transparent"]14:15[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]19:00[/TD]
[TD]20:00[/TD]
[TD]FALSE[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]16:17[/TD]
[TD="bgcolor: transparent"]16:57[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]7:38[/TD]
[TD]8:45[/TD]
[TD]FALSE[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]11:36[/TD]
[TD="bgcolor: transparent"]12:34[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]19:11[/TD]
[TD]20:49[/TD]
[TD]FALSE[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]9:17[/TD]
[TD="bgcolor: transparent"]10:19[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]16:04[/TD]
[TD]16:59[/TD]
[TD]FALSE[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]17:41[/TD]
[TD="bgcolor: transparent"]18:52[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]6:11[/TD]
[TD]7:09[/TD]
[TD]FALSE[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]14:50[/TD]
[TD="bgcolor: transparent"]15:40[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]14:47[/TD]
[TD]15:42[/TD]
[TD]FALSE[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]20:50[/TD]
[TD="bgcolor: transparent"]22:25[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]13:49[/TD]
[TD]14:34[/TD]
[TD]FALSE[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]16:43[/TD]
[TD="bgcolor: transparent"]17:29[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]19:39[/TD]
[TD]20:26[/TD]
[TD]FALSE[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]11:59[/TD]
[TD="bgcolor: transparent"]13:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]9:00[/TD]
[TD]9:40[/TD]
[TD]FALSE[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]11:30[/TD]
[TD="bgcolor: transparent"]12:50[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]8:57[/TD]
[TD]9:37[/TD]
[TD]FALSE[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]22:25[/TD]
[TD="bgcolor: transparent"]6:30[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]-39[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]19:17[/TD]
[TD]20:49[/TD]
[TD]FALSE[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]20:44[/TD]
[TD="bgcolor: transparent"]21:46[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]15:54[/TD]
[TD]17:08[/TD]
[TD]FALSE[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]14:50[/TD]
[TD="bgcolor: transparent"]15:52[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]14:20[/TD]
[TD]15:30[/TD]
[TD]FALSE[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]18:13[/TD]
[TD="bgcolor: transparent"]19:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]15:44[/TD]
[TD]17:00[/TD]
[TD]FALSE[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]16:59[/TD]
[TD="bgcolor: transparent"]18:15[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]11:23[/TD]
[TD]12:30[/TD]
[TD]FALSE[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]10:00[/TD]
[TD="bgcolor: transparent"]11:05[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]18:53[/TD]
[TD]20:40[/TD]
[TD]FALSE[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]19:53[/TD]
[TD="bgcolor: transparent"]9:00[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]-26[/TD]
[/TR]
[TR]
[TD]zzz[/TD]
[TD]8:45[/TD]
[TD]10:10[/TD]
[TD]FALSE[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]zzz[/TD]
[TD="bgcolor: transparent"]12:10[/TD]
[TD="bgcolor: transparent"]13:25[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
J
  • Question Question
How to Find Time Overlaps
2
Replies
10
Views
767
JK_NMS
J
S
  • Question Question
Using VBA to Flag and Calculate Overlapping Shift Times
Replies
0
Views
486
Snort
S
ibmy
  • Question Question
How to structure the formula properly?
Replies
2
Views
247
ibmy
ibmy
M
  • Solved
How to merge files?
Replies
2
Views
389
mrdigitalis
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top