|
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 |
| All times are GMT +1. The time now is 12:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com