Help with formula

Question

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

oliverscheurich · Answer

RobynsNest&nbsp;=SUMPRODUCT(B2:Y2,B19:Y19)You can try&nbsp;SUMPRODUCT.&nbsp;

hansvogelaar · Answer

RobynsNest&nbsp;
You can use the SUMPRODUCT function for this:
&nbsp;
=SUMPRODUCT(B2:B19,Y2:Y19)

robynsnest · Answer

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

hansvogelaar · Answer

RobynsNest&nbsp;
See OliverScheurich's reply.

robynsnest · Answer

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

Forum Discussion

Help with formula

6 Replies

Resources