How to loop through cells in an Excel formula (without VB)

Question

I have to loop through a row of cells and multiple two cell and add up them all up. Right now I am using this formula but want a way to create a loop that I can control the start and end cell. Today each time I add a row to the list I have to manually update the formula.

=(J49*$Q49)+(J50*$Q50)+(J51*$Q51)+(J52*$Q52)+(J53*$Q53)+(J54*$Q54)+(J55*$Q55)

I would like to have it dynamically start at row start at row 49 (by reading this value from another location) and end at row 55 (again reading it from a location).

OR I provide the initial start/end numbers but if I add a row it automatically adds it the formula.

Thank you.

Answer 1

That formula is equivalent to:

=SUMPRODUCT(J49:J55,$Q49:$Q55)

is that easy enough to edit? If not, perhaps you could use a table or dynamic named range?

Answer 2

Here is one formula approach:

=SUMPRODUCT(N(INDIRECT("J"&SEQUENCE(F44-F43+1,,F43,1))),N(INDIRECT("Q"&SEQUENCE(F44-F43+1,,F43,1))))

I'm not a fan of INDIRECT so I see if I can come up with a version using INDEX.

Answer 3

I simplified my earlier solution to:

=LET(S,SEQUENCE(F44-F43+1,,F43,1),SUMPRODUCT(N(INDIRECT("J"&S)),N(INDIRECT("Q"&S))))

Answer 4

Here is a way to eliminate the INDIRECT:

=LET(S,SEQUENCE(F44-F43+1,,F43,1),SUMPRODUCT(CHOOSEROWS(J:J,S),CHOOSEROWS(Q:Q,S)))

Answer 5

=SUMPRODUCT(FILTER(G:G,(ROW(G:G)>S41)*(ROW(G:G)<S42)),FILTER(Q:Q,(ROW(Q:Q)>S41)*(ROW(Q:Q)<S42)))