Help with formula

Copper Contributor

Help with formula

Hi. I am pretty much an excel novice and am struggling to get it to do a calculation that I need.

I need to do a sum of the value in B2 multiplied by B19, all the way along so that I have a total of all the 2 cells multiplied by the 19 cells. I'm not sure if that is making sense.

What I have typed in the formula bar is this:

=B2*B19:Y2*Y19

However once I press enter it says #VALUE! and it has changed the formula I have written to this:

=B2*B2:Y19*Y19

Can anyone tell me what I am doing wrong/if what I want to do is possible? I am hoping for a solution that can be transferred between different excel sheets, if that makes sense, as I will not always have the same number of columns. Thanks

6 Replies

Re: Help with formula

``=SUMPRODUCT(B2:Y2,B19:Y19)``

You can try SUMPRODUCT.

Re: Help with formula

You can use the SUMPRODUCT function for this:

=SUMPRODUCT(B2:B19,Y2:Y19)

Re: Help with formula

Hi. I just tried this and it did not give the correct result. I am not sure if I have made myself very clear.
I need to multiply cells 2 x 19 before I add them.
So B2*B19 + C2*C19 + D2*D19 etc

Re: Help with formula

Ah, I had thought he had written the same but I see it is different now.

Thanks so much @OliverScheurich, that has saved my a lot of hassle.

Can I ask one more thing please? I now need to multiply the total of that previous formula by 1.2. Is there a way to add that into the formula?

Thanks

Re: Help with formula

=1.2*SUMPRODUCT(B2:Y2,B9:Y19)