Forum Discussion
Solved
Shifting Formulas
Hello, how do I create a formula that, when shifted down, instead of referencing the values in the row below, references values that are six rows below? In my specific case, this is the formula I need to use: =SUMPRODUCT(D5:D7,E5:E7)
and instead of dragging it down and making it:
=SUMPRODUCT(D6:D8,E6:E8)
I would need it to be:
=SUMPRODUCT(D11:D13,E11:E13)
Thank you.
Let's say the first formula is in row 2.
=SUMPRODUCT(INDEX(D:D, 6*(ROW(A2)-ROW($A$2))+5):INDEX(D:D, 6*(ROW(A2)-ROW($A$2))+7), INDEX(E:E, 6*(ROW(A2)-ROW($A$2))+5):INDEX(E:E, 6*(ROW(A2)-ROW($A$2))+7))
Using a very different programming style that relies upon 365 functionality, one could have
= LET( products, TAKE(WRAPROWS(colD * colE, 6),,3), BYROW(products, LAMBDA(x, SUM(x))) )that would sum the column D and E products for the entire range.
Let's say the first formula is in row 2.
=SUMPRODUCT(INDEX(D:D, 6*(ROW(A2)-ROW($A$2))+5):INDEX(D:D, 6*(ROW(A2)-ROW($A$2))+7), INDEX(E:E, 6*(ROW(A2)-ROW($A$2))+5):INDEX(E:E, 6*(ROW(A2)-ROW($A$2))+7))
