• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple column multiply sum

M

mrzoogle

Member
Hi Excel Gurus,


I would like your help on solving the issue I have with multiplying columns and then sum the results.


As there are too many columns I can't keep changing formula always. I need a dynamic formula to do the calculation.


Please refer to the attached spreadsheet for more detail and let me know if you have any questions.


http://www.mediafire.com/view/?iz7q0zc5tntd591


Kind Regards,


Z.
 
Hi, mrzoogle!


Try this:

=SUMAPRODUCTO($A2:$A7*C2:C7) -----> in english: =SUMPRODUCT($A2:$A7*C2:C7)

and change A5 cell value to 0, formatting column A accordingly to 'General;General;"-"' (unapostrophed) to keep original display.


Note that I fixed column A with $ sign, for if you intend to copy it across columns, but adjust those details to your needs.


Regards!
 
Hi mrzoogle,


I think should be like:


Code: 
=SUMPRODUCT(($B$2:$B$7<>$C$11)*($A$2:$A$7)*($C$1:$P$1=$C$10)*(C2:P7))


...where $C$11 = "Prod X" and $C$10 = A1 referring to your sheet, should give you the desired result, and replace "-" by zero as SirJB7 advised.


Hi SirJB7!!

:)


Regards,

Faseeh
 
@Faseeh

Hi!

:)

Regards!

PS: Should have I understood so wrongly what mrzoogle posted? I get the same result as provided example.
 
Hi SirJB7,


Thanks for this, lol small things are hard to spot.


Trust me I tried alot of approach and the issue was "-" :)


Below is the formulae which solves exactly my problem


=SUMPRODUCT(($C$1:$P$1=D$16)*$A2:$A7*C2:p7)


SirJB7 you are star, as always !!


Kind Regards,


Z.
 
@Faseeh, thanks, your approach works as well.


Since we have changed prod X comm to zero, the problem solves itself ? as mentioned in my formula.


Please if you could enlighten me that would be great.


You are star as well. hehee :)
 
Hi SirJb7,


You understood it correctly, you formula will give error if you don't place zero to ignore the product X, I think the one i mentioned does not need that.


Regards,
 
@Faseeh

Hi!

I think I avoided the error with the replacement of the string "-" in cell A5 by a 0 (zero) value plus an edit format for column A.

Regards!
 
Hi, mrzoogle!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: we were just about to miss you :)
 
Hi mrzoogle,

:) :D

When you use symbol "<>" instead of "=" the formula will look for everything else then Prod X. so the formula will ignore Prod. X any ways, but for "-" it will give you an error. and what if, if you donot have a Zero for Prod. X, try with your current formula and it will give result, but wrong one. (replace zero with 1 and see)


@SirJb7,

;)


Regards,
 
You must log in or register to reply here.
Back
Top