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

	A​	B​	C​	D​	E​	F​	G​
1​		Time	N to Sh	S to Bl			Formula in F2 copied down
2​	01​	08:00:00​	0​	0​	N		=IF(E2="CAR",ROWS(E$2:E2)-SUM(F$1:F1)-COUNTIF(E$2:E2,"CAR"),"")
3​	02​	08:00:01​	0​	0​	N
4​	02​	08:00:02​	N to Sh	0​	CAR	2​
5​	02​	08:00:03​	0​	0​	N
6​	02​	08:00:04​	0​	0​	N
7​	02​	08:00:05​	0​	0​	N
8​	02​	08:00:06​	0​	0​	N
9​	02​	08:00:07​	0​	0​	N
10​	02​	08:00:08​	N to Sh	0​	CAR	5​
11​	02​	08:00:09​	N to Sh	0​	CAR	0​
12​	02​	08:00:10​	0​	0​	N
13​	02​	08:00:11​	0​	0​	N
14​	02​	08:00:12​	0​	0​	N
15​	02​	08:00:13​	N to Sh	0​	CAR	3​
16​	02​	08:00:14​	0​	0​	N
17​	02​	08:00:15​	0​	0​	N

Sheet: Sheet1

 

[Joe371]

That is perfect!! Thank you