Hi!

If the first column contains only "x123":

Array entered:

=LARGE(IF(D1:D4=4,C1:C4),1)-LARGE(IF(D1:D4=4,C1:C4),2)

If the first column may contain other ID's:

Also array entered:

=LARGE(IF((A1:A4="X123")*(D1:D4=4),C1:C4),1)-LARGE(IF((A1:A4="X123")*(D1:D4=4),C1:C4),2)

Biff

"cjjoo" wrote in
message ...

hi all,

the formula given is good but i realise that if the next time x123 has
a tyre replacement at location 4, the result is not what i desired.

the scenario:

x123 56 65 4
x123 67 75 1
x123 75 89 4
x123 89 100 4

the result : 100-89=11

can the sumproduct do this ?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=474676