sumproduct and even numbered rows

[Jack Sons]

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 Kuunders]

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.

 

[Bob Phillips]

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

 

[Jack Sons]

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

Jack.

 

[Bill Kuunders]

Thanks Bob,

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

Learning every day.

Bill

 

[RagDyer]

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


Regards,

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

Thanks Bob,

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

Learning every day.

Bill

 

[Bill Kuunders]

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