Formula - Sum and multiplying - Dynamic array spilled behavior

[ttch]

Hi all. I have a question about dynamic array. A workbook is attached. The product, volume, and price in "Calculation" are dynamic array linked to "Drivers". When a new row (new product) is added between product b and c in "Drivers", the formula (multiplying, sum) in "Calculation" are messed up. What is the proper way to set up the formula so they could move with the dynamic array? The desired output should look like the attached image. I hope my question is clear. Thank you for looking into this.

[Bo_Ry]

Please try

=LET(d,A1:C5,ro,SEQUENCE(ROWS(d)+1),c,SEQUENCE(,COLUMNS(d)+1),
r,IFERROR(IF(c>COLUMNS(d),INDEX(d,,2)*INDEX(d,,3),d),"Revenue"),
t,IF(c=1,"Total",IF(c=2,"",MMULT(SEQUENCE(,ROWS(r),,0),N(+r)))),IF(ro>ROWS(d),t,r))

[ttch]

Thank you very much Bo_Ry. I have one follow up question. Could you please also help look into the attached workbook? One additional sheet "Result" is added where I would like to list the total volume and revenue calculated in "Calculation". However once a row is added in "Drivers", the references in "Result" do not shift. The reference of volume and revenue should move to C7 and D7 in "Calculation". Is there a way to achieve this? Thank you.

[Bo_Ry]

Please Try

=LOOKUP(9^9,INDEX(Calculation!A1#,,3))

and
=LOOKUP(9^9,INDEX(Calculation!A1#,,4))

[ttch]

Please Try

=LOOKUP(9^9,INDEX(Calculation!A1#,,3))

and
=LOOKUP(9^9,INDEX(Calculation!A1#,,4))

This works well. Thank you very much.