Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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: 

=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

@RobynsNest 

=SUMPRODUCT(B2:Y2,B19:Y19)

You can try SUMPRODUCT.

sumproduct.JPG 

@RobynsNest 

You can use the SUMPRODUCT function for this:

 

=SUMPRODUCT(B2:B19,Y2:Y19)

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

@RobynsNest 

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?

Thanks

@RobynsNest 

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