sumproduct and even numbered rows

Hi all,

In column C I have amounts (e.g. 2345,89) in even numbered rows beginning
with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in odd
numbered rows, C5 up to C99. For instance:

C4 123.45
C5 8
C6 33.91
C7 3
C8 0.88
C9 3
C10 14.47
C11 28
C12 3
C13 16

etc.

In H8 I have one of the codenumbers, say 3. I want in H9 the sum of all
amounts that are followed by that codenumber. That sum is in the example
above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a
codenumber, because C12 is an even numbered row).

I tried the following formula, in H9 but to no avail. What is wrong?

=SUMPRODUCT(($C$5:$C$99=H8)*(MOD(ROW($C$5:$C$99);1)=0)*($C$4:$C$98))

Jack Sons
The Netherlands

Looking for uneven rows like 11
i.e when divided by 2 the rest will be 1
see mod(row(c5:c99),2)=1

=SUMPRODUCT(--(C5:C99=H8)*(--(MOD(ROW(C5:C99),2)=1)),C4:C98)
Got your required total of 34.79
Hope this works for the larger sample.
--
Groeten vanuit Nieuw Zeeland

Willy Kuunders

"Jack Sons" <j.sons@planet.nl> wrote in message
news:OUpbK6JNFHA.2680@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> In column C I have amounts (e.g. 2345,89) in even numbered rows beginning
> with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in odd
> numbered rows, C5 up to C99. For instance:
>
> C4 123.45
> C5 8
> C6 33.91
> C7 3
> C8 0.88
> C9 3
> C10 14.47
> C11 28
> C12 3
> C13 16
>
> etc.
>
> In H8 I have one of the codenumbers, say 3. I want in H9 the sum of all
> amounts that are followed by that codenumber. That sum is in the example
> above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a
> codenumber, because C12 is an even numbered row).
>
> I tried the following formula, in H9 but to no avail. What is wrong?
>
> =SUMPRODUCT(($C$5:$C$99=H8)*(MOD(ROW($C$5:$C$99);1)=0)*($C$4:$C$98))
>
> Jack Sons
> The Netherlands
>
>

Hi Bill,

Don't need the * and --, they do the same thing

=SUMPRODUCT(--(C5:C98=H8),--(MOD(ROW(C5:C98),2)=1),C4:C97)

Regards

Bob

"Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
news:e1%23uaeKNFHA.1308@TK2MSFTNGP15.phx.gbl...
> Looking for uneven rows like 11
> i.e when divided by 2 the rest will be 1
> see mod(row(c5:c99),2)=1
>
> =SUMPRODUCT(--(C5:C99=H8)*(--(MOD(ROW(C5:C99),2)=1)),C4:C98)
> Got your required total of 34.79
> Hope this works for the larger sample.
> --
> Groeten vanuit Nieuw Zeeland
>
> Willy Kuunders
>
> "Jack Sons" <j.sons@planet.nl> wrote in message
> news:OUpbK6JNFHA.2680@TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > In column C I have amounts (e.g. 2345,89) in even numbered rows
beginning
> > with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in
odd
> > numbered rows, C5 up to C99. For instance:
> >
> > C4 123.45
> > C5 8
> > C6 33.91
> > C7 3
> > C8 0.88
> > C9 3
> > C10 14.47
> > C11 28
> > C12 3
> > C13 16
> >
> > etc.
> >
> > In H8 I have one of the codenumbers, say 3. I want in H9 the sum of all
> > amounts that are followed by that codenumber. That sum is in the example
> > above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a
> > codenumber, because C12 is an even numbered row).
> >
> > I tried the following formula, in H9 but to no avail. What is wrong?
> >
> > =SUMPRODUCT(($C$5:$C$99=H8)*(MOD(ROW($C$5:$C$99);1)=0)*($C$4:$C$98))
> >
> > Jack Sons
> > The Netherlands
> >
> >
>
>

Bill, hartelijk dank vanuit Den Haag, werkt perfect.
Bob, thank you very much, works good.
Problem solved.

Jack.

"Jack Sons" <j.sons@planet.nl> schreef in bericht
news:OUpbK6JNFHA.2680@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> In column C I have amounts (e.g. 2345,89) in even numbered rows beginning
> with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in odd
> numbered rows, C5 up to C99. For instance:
>
> C4 123.45
> C5 8
> C6 33.91
> C7 3
> C8 0.88
> C9 3
> C10 14.47
> C11 28
> C12 3
> C13 16
>
> etc.
>
> In H8 I have one of the codenumbers, say 3. I want in H9 the sum of all
> amounts that are followed by that codenumber. That sum is in the example
> above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a
> codenumber, because C12 is an even numbered row).
>
> I tried the following formula, in H9 but to no avail. What is wrong?
>
> =SUMPRODUCT(($C$5:$C$99=H8)*(MOD(ROW($C$5:$C$99);1)=0)*($C$4:$C$98))
>
> Jack Sons
> The Netherlands
>
>

Thanks Bob,

Yours looks tidy.
I have seen a lot of examples where people use the )*( in stead of a ,

Learning every day.

Bill


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u%23dcV5KNFHA.1040@TK2MSFTNGP12.phx.gbl...
> Hi Bill,
>
> Don't need the * and --, they do the same thing
>
> =SUMPRODUCT(--(C5:C98=H8),--(MOD(ROW(C5:C98),2)=1),C4:C97)
>
> Regards
>
> Bob
>
> "Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
> news:e1%23uaeKNFHA.1308@TK2MSFTNGP15.phx.gbl...
>> Looking for uneven rows like 11
>> i.e when divided by 2 the rest will be 1
>> see mod(row(c5:c99),2)=1
>>
>> =SUMPRODUCT(--(C5:C99=H8)*(--(MOD(ROW(C5:C99),2)=1)),C4:C98)
>> Got your required total of 34.79
>> Hope this works for the larger sample.
>> --
>> Groeten vanuit Nieuw Zeeland
>>
>> Willy Kuunders
>>
>> "Jack Sons" <j.sons@planet.nl> wrote in message
>> news:OUpbK6JNFHA.2680@TK2MSFTNGP09.phx.gbl...
>> > Hi all,
>> >
>> > In column C I have amounts (e.g. 2345,89) in even numbered rows
> beginning
>> > with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in
> odd
>> > numbered rows, C5 up to C99. For instance:
>> >
>> > C4 123.45
>> > C5 8
>> > C6 33.91
>> > C7 3
>> > C8 0.88
>> > C9 3
>> > C10 14.47
>> > C11 28
>> > C12 3
>> > C13 16
>> >
>> > etc.
>> >
>> > In H8 I have one of the codenumbers, say 3. I want in H9 the sum of
>> > all
>> > amounts that are followed by that codenumber. That sum is in the
>> > example
>> > above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a
>> > codenumber, because C12 is an even numbered row).
>> >
>> > I tried the following formula, in H9 but to no avail. What is wrong?
>> >
>> > =SUMPRODUCT(($C$5:$C$99=H8)*(MOD(ROW($C$5:$C$99);1)=0)*($C$4:$C$98))
>> >
>> > Jack Sons
>> > The Netherlands
>> >
>> >
>>
>>
>
>

IMHO, the asterisk is better then the unary, wherever possible!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
news:ehYTGzWNFHA.3704@TK2MSFTNGP12.phx.gbl...
Thanks Bob,

Yours looks tidy.
I have seen a lot of examples where people use the )*( in stead of a ,

Learning every day.

Bill


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:u%23dcV5KNFHA.1040@TK2MSFTNGP12.phx.gbl...
> Hi Bill,
>
> Don't need the * and --, they do the same thing
>
> =SUMPRODUCT(--(C5:C98=H8),--(MOD(ROW(C5:C98),2)=1),C4:C97)
>
> Regards
>
> Bob
>
> "Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
> news:e1%23uaeKNFHA.1308@TK2MSFTNGP15.phx.gbl...
>> Looking for uneven rows like 11
>> i.e when divided by 2 the rest will be 1
>> see mod(row(c5:c99),2)=1
>>
>> =SUMPRODUCT(--(C5:C99=H8)*(--(MOD(ROW(C5:C99),2)=1)),C4:C98)
>> Got your required total of 34.79
>> Hope this works for the larger sample.
>> --
>> Groeten vanuit Nieuw Zeeland
>>
>> Willy Kuunders
>>
>> "Jack Sons" <j.sons@planet.nl> wrote in message
>> news:OUpbK6JNFHA.2680@TK2MSFTNGP09.phx.gbl...
>> > Hi all,
>> >
>> > In column C I have amounts (e.g. 2345,89) in even numbered rows
> beginning
>> > with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in
> odd
>> > numbered rows, C5 up to C99. For instance:
>> >
>> > C4 123.45
>> > C5 8
>> > C6 33.91
>> > C7 3
>> > C8 0.88
>> > C9 3
>> > C10 14.47
>> > C11 28
>> > C12 3
>> > C13 16
>> >
>> > etc.
>> >
>> > In H8 I have one of the codenumbers, say 3. I want in H9 the sum of
>> > all
>> > amounts that are followed by that codenumber. That sum is in the
>> > example
>> > above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a
>> > codenumber, because C12 is an even numbered row).
>> >
>> > I tried the following formula, in H9 but to no avail. What is wrong?
>> >
>> > =SUMPRODUCT(($C$5:$C$99=H8)*(MOD(ROW($C$5:$C$99);1)=0)*($C$4:$C$98))
>> >
>> > Jack Sons
>> > The Netherlands
>> >
>> >
>>
>>
>
>

Somewhere along the line things must have changed.
selecting fx does show sumproduct(array1,array2,array3,...............)
I hadn't looked at that before.

Regards
Bill K

"RagDyer" <ragdyer@cutoutmsn.com> wrote in message
news:egjwoYXNFHA.3900@TK2MSFTNGP10.phx.gbl...
> IMHO, the asterisk is better then the unary, wherever possible!
> --
>
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit!
> -------------------------------------------------------------------
>
> "Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
> news:ehYTGzWNFHA.3704@TK2MSFTNGP12.phx.gbl...
> Thanks Bob,
>
> Yours looks tidy.
> I have seen a lot of examples where people use the )*( in stead of a ,
>
> Learning every day.
>
> Bill
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:u%23dcV5KNFHA.1040@TK2MSFTNGP12.phx.gbl...
>> Hi Bill,
>>
>> Don't need the * and --, they do the same thing
>>
>> =SUMPRODUCT(--(C5:C98=H8),--(MOD(ROW(C5:C98),2)=1),C4:C97)
>>
>> Regards
>>
>> Bob
>>
>> "Bill Kuunders" <bill.kuunders@xtra.co.nz> wrote in message
>> news:e1%23uaeKNFHA.1308@TK2MSFTNGP15.phx.gbl...
>>> Looking for uneven rows like 11
>>> i.e when divided by 2 the rest will be 1
>>> see mod(row(c5:c99),2)=1
>>>
>>> =SUMPRODUCT(--(C5:C99=H8)*(--(MOD(ROW(C5:C99),2)=1)),C4:C98)
>>> Got your required total of 34.79
>>> Hope this works for the larger sample.
>>> --
>>> Groeten vanuit Nieuw Zeeland
>>>
>>> Willy Kuunders
>>>
>>> "Jack Sons" <j.sons@planet.nl> wrote in message
>>> news:OUpbK6JNFHA.2680@TK2MSFTNGP09.phx.gbl...
>>> > Hi all,
>>> >
>>> > In column C I have amounts (e.g. 2345,89) in even numbered rows
>> beginning
>>> > with C4 up to C98 and corresponding codenumbers (integers 1 to 46) in
>> odd
>>> > numbered rows, C5 up to C99. For instance:
>>> >
>>> > C4 123.45
>>> > C5 8
>>> > C6 33.91
>>> > C7 3
>>> > C8 0.88
>>> > C9 3
>>> > C10 14.47
>>> > C11 28
>>> > C12 3
>>> > C13 16
>>> >
>>> > etc.
>>> >
>>> > In H8 I have one of the codenumbers, say 3. I want in H9 the sum of
>>> > all
>>> > amounts that are followed by that codenumber. That sum is in the
>>> > example
>>> > above clearly 33.91 + 0.88 = 34.79 (The 3 in C12 is an amount, not a
>>> > codenumber, because C12 is an even numbered row).
>>> >
>>> > I tried the following formula, in H9 but to no avail. What is wrong?
>>> >
>>> > =SUMPRODUCT(($C$5:$C$99=H8)*(MOD(ROW($C$5:$C$99);1)=0)*($C$4:$C$98))
>>> >
>>> > Jack Sons
>>> > The Netherlands
>>> >
>>> >
>>>
>>>
>>
>>
>
>