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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" schreef in bericht
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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