Sumproduct?

[cjjoo]

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?

[Biff]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

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

but I get 23


--
HTH

Bob Phillips

"cjjoo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Richard Buttrey]

On Mon, 10 Oct 2005 09:29:05 +0100, "Bob Phillips"
<[email protected]> 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
__________________________

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
> On Mon, 10 Oct 2005 09:29:05 +0100, "Bob Phillips"
> <[email protected]> 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
> __________________________

[Sandy Mann]

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
[email protected]
Replace@mailinator with @tiscali.co.uk


"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>
>

[cjjoo]

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 ?

[Biff]

I don't see it.

???

Biff

"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> Replace@mailinator with @tiscali.co.uk
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> 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
>>>
>>
>>
>
>

[Biff]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Sandy Mann]

Hi Biff,

"Biff" <[email protected]> wrote in message
news:[email protected]...
>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
[email protected]
Replace@mailinator with @tiscali.co.uk


"Biff" <[email protected]> wrote in message
news:[email protected]...
>I don't see it.
>
> ???
>
> Biff
>
> "Sandy Mann" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> [email protected]
>> Replace@mailinator with @tiscali.co.uk
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in
>>> message news:[email protected]...
>>>>
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>