sum product in an index possibly?

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
 
B

Bob Phillips

=SUMPRODUCT(--(B1:B100="n"),C1:C100,D1:D100)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Tim C

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List of Direct and Indirect Reports 4
Scrabble Value calculation for Welsh words 0
create a "variable array?" to report data 1
sumif or sum production - 3 or more criteria 5
Multiple Criteria, Count If, Sum Product to get count across range 5
Problem with a Array formula, Please help !! 6
sumif & sumproduct 7
Condition Format with value of other column 2

Top