Count of gap between cells

[Joe371]

Hi,

I have had a look through but couldn't find the answer to my problem.
I have a table that has the time a car drives along a road in each direction, I have got this into one column, so that you can analyse the gap between cars.

I have attached an image. https://drive.google.com/open?id=1Nx2ScM_eNhyAFbzwfIVBnnbyMVfFFBje

At each point in the table it says "Car", I want the total of "N" above it until the previous Car, thus showing me the total gap in seconds;

Thank you very much for an help.

Joe

 

[Yongle]

In F2
=IF(E2="CAR",ROWS(E$2:E2)-SUM(F$1:F1)-COUNTIF(E$2:E2,"CAR"),"")


Are these the values which should be returned ?

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td]Time[/td][td]N to Sh[/td][td]S to Bl[/td][td][/td][td][/td][td]Formula in F2 copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

01​

[/td][td]

08:00:00​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td] =IF(E2="CAR",ROWS(E$2:E2)-SUM(F$1:F1)-COUNTIF(E$2:E2,"CAR"),"")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

02​

[/td][td]

08:00:01​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

02​

[/td][td]

08:00:02​

[/td][td]N to Sh[/td][td]

0​

[/td][td]CAR[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

02​

[/td][td]

08:00:03​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

02​

[/td][td]

08:00:04​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

02​

[/td][td]

08:00:05​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

02​

[/td][td]

08:00:06​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

02​

[/td][td]

08:00:07​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

02​

[/td][td]

08:00:08​

[/td][td]N to Sh[/td][td]

0​

[/td][td]CAR[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

02​

[/td][td]

08:00:09​

[/td][td]N to Sh[/td][td]

0​

[/td][td]CAR[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

02​

[/td][td]

08:00:10​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

02​

[/td][td]

08:00:11​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

02​

[/td][td]

08:00:12​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

02​

[/td][td]

08:00:13​

[/td][td]N to Sh[/td][td]

0​

[/td][td]CAR[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

02​

[/td][td]

08:00:14​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

02​

[/td][td]

08:00:15​

[/td][td]

0​

[/td][td]

0​

[/td][td]N[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Joe371]

That is perfect!! Thank you