sum product in an index possibly?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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
 
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
 
Back
Top