THEIS IS MY PROBLEM:


vehicle no start km end km tyre location
x123 56 65 4
x123 65 75 1
x123 75 89 4


i want to sum the distance the vehicle x123 has travelled when tyre
location is 4.

the result: 89-65=34.

Can i use sum product?


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

Hi!

the result: 89-65=34.

You sure it isn't 24?

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF((A1:A3="x123")*(D1:D3=4),C1:C3))-MIN(IF((A1:A3="x123")*(D1:D3=4),C1:C3))

Biff

"cjjoo" wrote in
message ...

THEIS IS MY PROBLEM:


vehicle no start km end km tyre location
x123 56 65 4
x123 65 75 1
x123 75 89 4


i want to sum the distance the vehicle x123 has travelled when tyre
location is 4.

the result: 89-65=34.

Can i use sum product?


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

=SUMPRODUCT(--(A2:A20="x123"),--(D2:D20=4),C2:C20-B2:B20)

but I get 23


--
HTH

Bob Phillips

"cjjoo" wrote in
message ...

THEIS IS MY PROBLEM:


vehicle no start km end km tyre location
x123 56 65 4
x123 65 75 1
x123 75 89 4


i want to sum the distance the vehicle x123 has travelled when tyre
location is 4.

the result: 89-65=34.

Can i use sum product?


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

On Mon, 10 Oct 2005 09:29:05 +0100, "Bob Phillips"
wrote:

=SUMPRODUCT(--(A2:A20="x123"),--(D2:D20=4),C2:C20-B2:B20)

but I get 23

Just an observation, but presumably it needs a constant of +1 adding
??

I keep seeing these double negative signs appearing in these types of
formulae. They certainly don't work without them, but what's the
reason Excel requires them?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard,

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html. I hope
that will explain it all to you.

--
HTH

Bob Phillips

"Richard Buttrey" wrote in
message ...
On Mon, 10 Oct 2005 09:29:05 +0100, "Bob Phillips"
wrote:

=SUMPRODUCT(--(A2:A20="x123"),--(D2:D20=4),C2:C20-B2:B20)

but I get 23

Just an observation, but presumably it needs a constant of +1 adding
??

I keep seeing these double negative signs appearing in these types of
formulae. They certainly don't work without them, but what's the
reason Excel requires them?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Biff,

I assume that you have worked out by now that there is a flaw in the OP's
logic that by giving him/her what he/she asked for you have continued.

--
Rergards


Sandy

Replace@mailinator with @tiscali.co.uk


"Biff" wrote in message
...
Hi!

the result: 89-65=34.

You sure it isn't 24?

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF((A1:A3="x123")*(D1:D3=4),C1:C3))-MIN(IF((A1:A3="x123")*(D1:D3=4),C1:C3))

Biff

"cjjoo" wrote in
message ...

THEIS IS MY PROBLEM:


vehicle no start km end km tyre location
x123 56 65 4
x123 65 75 1
x123 75 89 4


i want to sum the distance the vehicle x123 has travelled when tyre
location is 4.

the result: 89-65=34.

Can i use sum product?


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

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

I don't see it.

???

Biff

"Sandy Mann" wrote in message
...
Biff,

I assume that you have worked out by now that there is a flaw in the OP's
logic that by giving him/her what he/she asked for you have continued.

--
Rergards


Sandy

Replace@mailinator with @tiscali.co.uk


"Biff" wrote in message
...
Hi!

the result: 89-65=34.

You sure it isn't 24?

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF((A1:A3="x123")*(D1:D3=4),C1:C3))-MIN(IF((A1:A3="x123")*(D1:D3=4),C1:C3))

Biff

"cjjoo" wrote in
message ...

THEIS IS MY PROBLEM:


vehicle no start km end km tyre location
x123 56 65 4
x123 65 75 1
x123 75 89 4


i want to sum the distance the vehicle x123 has travelled when tyre
location is 4.

the result: 89-65=34.

Can i use sum product?


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

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

Hi Biff,

"Biff" wrote in message
...
I don't see it.

No, it was me that didn't see it <g

The OP said:

vehicle no start km end km tyre location
x123 56 65 4
x123 65 75 1
x123 75 89 4


i want to sum the distance the vehicle x123 has travelled when tyre
location is 4.

the result: 89-65=34.

As I, (and I think Bob), read it, the OP was asking for the total kilometres
dirven while the tyre location was 4
ie (65-56) + (89-75) = 23

I obviously misunderstood what the OP was meaning by 'tyre location' and I
assume now, (as you saw from the start), that the tyre was changed at 65 and
again at 89 kilometres which is what you formula correctly returned.

--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk


"Biff" wrote in message
...
I don't see it.

???

Biff

"Sandy Mann" wrote in message
...
Biff,

I assume that you have worked out by now that there is a flaw in the OP's
logic that by giving him/her what he/she asked for you have continued.

--
Rergards


Sandy

Replace@mailinator with @tiscali.co.uk


"Biff" wrote in message
...
Hi!

the result: 89-65=34.

You sure it isn't 24?

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF((A1:A3="x123")*(D1:D3=4),C1:C3))-MIN(IF((A1:A3="x123")*(D1:D3=4),C1:C3))

Biff

"cjjoo" wrote in
message ...

THEIS IS MY PROBLEM:


vehicle no start km end km tyre location
x123 56 65 4
x123 65 75 1
x123 75 89 4


i want to sum the distance the vehicle x123 has travelled when tyre
location is 4.

the result: 89-65=34.

Can i use sum product?


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