Shifting Formulas

[AhmedDeLacroix]

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.

[#2 · Accepted solution]

@AhmedDeLacroix 

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))

[#3]

@AhmedDeLacroix 

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.