|
sum product in an index possibly?
trying to do a formula along the lines of: search column b for "n", if its an
"n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
=SUMPRODUCT(--(B1:B100="n"),C1:C100,D1:D100)
-- HTH RP (remove nothere from the email address if mailing direct) "CMAC" wrote in message ... trying to do a formula along the lines of: search column b for "n", if its an "n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
One way
=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) Regards Peo Sjoblom "CMAC" wrote: trying to do a formula along the lines of: search column b for "n", if its an "n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
Just curious. Why would you use
=SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) instead of =SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8)) Is there a speed issue? Or am I missing something else? Thanks, Tim C "Peo Sjoblom" wrote in message ... One way =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) Regards Peo Sjoblom "CMAC" wrote: trying to do a formula along the lines of: search column b for "n", if its an "n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
I wasn't consistent, Bob's formula was
http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Tim C" wrote in message ... Just curious. Why would you use =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) instead of =SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8)) Is there a speed issue? Or am I missing something else? Thanks, Tim C "Peo Sjoblom" wrote in message ... One way =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) Regards Peo Sjoblom "CMAC" wrote: trying to do a formula along the lines of: search column b for "n", if its an "n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
I multiply the terms "manually" instead of separating them with a comma,
thereby forcing the conversion without the double negative. I end up with a single term, with SUMPRODUCT taking care of the adding and the array handling. To me this results in a cleaner, more easily understood formula. Is this just a personal preference or are there advantages to doing it Bob's way? (My formula would be more concise if I took out the last set of parenthesis, but I think the intent of the formula is more easily understood with them in place. This can be very helpful for later troubleshooting or modification.) Tim C "Peo Sjoblom" wrote in message ... I wasn't consistent, Bob's formula was http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Tim C" wrote in message ... Just curious. Why would you use =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) instead of =SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8)) Is there a speed issue? Or am I missing something else? Thanks, Tim C "Peo Sjoblom" wrote in message ... One way =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) Regards Peo Sjoblom "CMAC" wrote: trying to do a formula along the lines of: search column b for "n", if its an "n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
Hi
Advantages of using the double minus: - slightly faster (approx. 5%) - no problems if you have text values in your range (e.g. a heading row). The multiplication syntax will fail the double minus not (will ignore text cells) But take a look at: http://www.xldynamic.com/source/xld....CT.html#format -- Regards Frank Kabel Frankfurt, Germany Tim C wrote: I multiply the terms "manually" instead of separating them with a comma, thereby forcing the conversion without the double negative. I end up with a single term, with SUMPRODUCT taking care of the adding and the array handling. To me this results in a cleaner, more easily understood formula. Is this just a personal preference or are there advantages to doing it Bob's way? (My formula would be more concise if I took out the last set of parenthesis, but I think the intent of the formula is more easily understood with them in place. This can be very helpful for later troubleshooting or modification.) Tim C "Peo Sjoblom" wrote in message ... I wasn't consistent, Bob's formula was http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Tim C" wrote in message ... Just curious. Why would you use =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) instead of =SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8)) Is there a speed issue? Or am I missing something else? Thanks, Tim C "Peo Sjoblom" wrote in message ... One way =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) Regards Peo Sjoblom "CMAC" wrote: trying to do a formula along the lines of: search column b for "n", if its an "n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
Frank,
Danke. Tim C "Frank Kabel" wrote in message ... Hi Advantages of using the double minus: - slightly faster (approx. 5%) - no problems if you have text values in your range (e.g. a heading row). The multiplication syntax will fail the double minus not (will ignore text cells) But take a look at: http://www.xldynamic.com/source/xld....CT.html#format -- Regards Frank Kabel Frankfurt, Germany Tim C wrote: I multiply the terms "manually" instead of separating them with a comma, thereby forcing the conversion without the double negative. I end up with a single term, with SUMPRODUCT taking care of the adding and the array handling. To me this results in a cleaner, more easily understood formula. Is this just a personal preference or are there advantages to doing it Bob's way? (My formula would be more concise if I took out the last set of parenthesis, but I think the intent of the formula is more easily understood with them in place. This can be very helpful for later troubleshooting or modification.) Tim C "Peo Sjoblom" wrote in message ... I wasn't consistent, Bob's formula was http://www.mcgimpsey.com/excel/formulae/doubleneg.html -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Tim C" wrote in message ... Just curious. Why would you use =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) instead of =SUMPRODUCT((B2:B8="n")*(C2:C8*D2:D8)) Is there a speed issue? Or am I missing something else? Thanks, Tim C "Peo Sjoblom" wrote in message ... One way =SUMPRODUCT(--(B2:B8="n"),(C2:C8*D2:D8)) Regards Peo Sjoblom "CMAC" wrote: trying to do a formula along the lines of: search column b for "n", if its an "n" then multilply column d by column c. I need it to include all occurances though. so in the exapmle below it would include eeee, hhhh, and kkkk. does anyone know what a proper formula would be? many thanks. A b c d eeee n 60 1 ffff y 55 2 gggg j 12 1 hhhh n 50 2 iiii j 14 3 jjjj y 14 1 kkkk n 50 1 |
| All times are GMT +1. The time now is 10:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com