excel multiplication and sub total

[luvbite38]

Hi guys,

It may be a most stupid question, you guy may ever came across.

I have cost of items in row 3 and then underneath, I have bunch of sold products under each item types / location. see example below pls.

existing:
A B C D
apple banana grapes Total
£40.00 £30.00 £70.00

9000 4 00 2300 £533,000.00
20000 4000 1 £920,070.00


current I am using this formula

=(A4*A$2+B4*B$2+C4*C$2)

Now my issue is that someone can add the items in this spreadsheet, I will have to manually add the refs in the formula. Is there a way I can optimise this formula and using the dynmic range. so if someone inserts a column formula in total will automaically include it in formula??

Any help pls

Thanks

 

[Scott Huish]

First, you can simplify your formulas with SUMPRODUCT, and I think this will also solve your problem:

=SUMPRODUCT(A4:C4,A2:C2)

 

[luvbite38]

Thanks mate,

Really appreciate it

but how can add a dynamic range, so if a column is add, it automatically adjust the range in the formula.

 

[Scott Huish]

Put the formula to the right of your data and it will adjust.

 

[luvbite38]

AWesome,

you're a super star .....

thanks yyou verrry muchhh