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 nee...
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.