Help with formula

Copper Contributor

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: 


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


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



You can try SUMPRODUCT.



You can use the SUMPRODUCT function for this:



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


See @OliverScheurich's reply.

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?