0

I am looking to build an Excel table listing funds which breaks down each one by its sector weightings in the columns which are manually inputted. In the summative row in each cell at the bottom of the table is the formula:

C18 =SUM($B$2*C2,$B$3*C3,$B$4*C4,$B$5*C5,$B$6*C6,$B$7*C7,$B$8*C8,$B$9*C9,$B$10*C10,$B$11*C11,$B$12*C12,$B$13*C13,$B$14*C14,$B$15*C15,$B$16*C16,$B$17*C17)/$B$18

D18 =SUM($B$2*D2,$B$3*D3,$B$4*D4,$B$5*D5,$B$6*D6,$B$7*D7,$B$8*D8,$B$9*D9,$B$10*D10,$B$11*D11,$B$12*D12,$B$13*D13,$B$14*D14,$B$15*D15,$B$16*D16,$B$17*D17)/$B$18

E18 =SUM($B$2*E2,$B$3*E3,$B$4*E4,$B$5*E5,$B$6*E6,$B$7*E7,$B$8*E8,$B$9*E9,$B$10*E10,$B$11*E11,$B$12*E12,$B$13*E13,$B$14*E14,$B$15*E15,$B$16*E16,$B$17*E17)/$B$18

Etc...

A little knowledge is a dangerous thing! I know just enough to know there is a much better way to do it, but that's it. It would be ideal if the formula updated itself when additional rows are added to the table.

enter image description here

BadDogTitan
  • 103
  • 1
  • 9

1 Answers1

1

SUGGESTION

Since you are getting the sums of products, you can use the =SUMPRODUCT() function instead to simplify

C18: =SUMPRODUCT($B$2:$B$17, C2:C17)
D18: =SUMPRODUCT($B$2:$B$17, D2:D17)
.
.

Note that this formula only covers the given range, and will not update itself if you want to insert new rows just above the TOTAL section. But if you insert rows anywhere between the given range, the values should update as well.

Mic Test
  • 106
  • 5
  • Hi there! Let me know if this answers your question, or if there are clarifications so that I can make some changes to my answer. – Mic Test Aug 23 '23 at 21:40