And to add to your confusion, and choices available, I came up with this one
which you can put into E2 and then fill down:
=IF(AND(N(D1)0,N(D2)0,N(B2)0),D2-OFFSET(A$1,MATCH(OFFSET(D2,0,-3),A$2:A$19,0),3),"")

Like Lori's formula, it gives cumulative mileage at each point. The AND()
portion checks that values in column D on current row and row above it are
numeric, and that the value in column B on same row is numeric (date). If
those 3 conditions are met, the value is calculated.

Also, a formula to use to get the subtotal at D6, D9, D14 and D20 is (place
in D6, then copy to each of the other cells)
=OFFSET(D6,-1,0)-OFFSET(A$1,MATCH(OFFSET(D6,-1,-3),A$2:A$25,0),3)
Although that will give you same answer that is last one for that truck in
column E so you could write it as
=Offset(D6,-1,1)



"Mr. Low" wrote:

Dear Sir,

I have the following worksheet :

A B C D
E
1 TRUCK DOC DATE QUANTITY METER MILEAGE
2 BDT7198 01/12/2006 204.00 294151
3 BDT7198 12/12/2006 299.00 294251
4 BDT7198 20/12/2006 295.00 294455
5 BDT7198 27/12/2006 273.00 294852
6 BDT7198 Quantity sub-total 1,071.00 =(D5-D2)

7 BEF4050 07/12/2006 100.00 447528
8 BEF4050 15/12/2006 102.00 448300
9 BEF4050 202.00
=(D8-D7)
10 BEK8152 12/12/2006 331.00 228919
11 BEK8152 15/12/2006 273.00 229455
12 BEK8152 20/12/2006 298.00 230060
13 BEK8152 27/12/2006 253.00 230486
14 BEK8152 1,155.00
=(D13-D10)
15 BEL6718 06/12/2006 287.00 613726
16 BEL6718 07/12/2006 281.00 613999
17 BEL6718 12/12/2006 329.00 614479
18 BEL6718 14/12/2006 293.00 614927
19 BEL6718 16/12/2006 309.00 615371
1,499.00
=(D19-D15)


There is a blank between different truch number, may I know what formula I
must enter at E2 and copy downwards to get the answers at E6, E9 and E13 ?

These answers are the last meter reading minus the first meter reading of
the same truck.

I try using formula : IF(A2=A3,"",D2-VLOOKUP(A2,A$2:D$19,4,FALSE)), but it
does not work.


Thanks
Low




--
A36B58K641