Shift Excel Formula References by Six Rows When Copying Down
Question details
Create a formula that can be copied down while shifting its source ranges by six rows for each result row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to calculate SUMPRODUCT values from matching three-row ranges in columns D and E, with a six-row gap between each referenced block.
- Observed behavior
- Normal relative references move down one row when copied, but each new formula must instead reference the next block six rows lower.
Confirm that the first output formula is in row 2 and that the first source block is D5:D7 with the matching values in E5:E7. Adjust the starting row numbers in the formula if your data begins elsewhere.
Use INDEX and ROW to move each source block down six rows
Use this formula when each copied result should calculate a three-row SUMPRODUCT block and the next result must start six rows lower.
INDEX returns the start and end cells dynamically, while ROW calculates how many six-row shifts are needed from the first formula row. Copying the formula down changes the output row but preserves the six-row block increment.
Click the cell in row 2 where you want the first result to appear, such as A2 or your chosen results column.
Type or paste =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)) and press Enter.
Drag the fill handle from the lower-right corner of the formula cell down the required number of rows, or copy the cell and paste it into the destination range.
Click the next copied formula and check the Formula Bar. The first formula uses D5:D7 and E5:E7; the next one calculates D11:D13 and E11:E13.

Create Six-Row Formula Reference Shifts in WPS Spreadsheets
WPS Spreadsheets supports INDEX, ROW, and SUMPRODUCT, so you can use the same dynamic formula pattern to calculate grouped ranges and copy the result down without manually editing each reference.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open your Excel workbook from the File menu.
- 2. Choose the first result cell: Click the first cell for the output, such as A2, where the formula should begin.
- 3. Enter the formula: Paste =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)) into the Formula Bar and press Enter.
- 4. Fill the formula down: Use the fill handle at the bottom-right of the selected cell to drag the formula down through the needed result rows.

Frequently Asked Questions
Why do normal Excel references move only one row when I copy a formula down?
Relative references are designed to change by the same number of rows as the copied formula. Use ROW inside INDEX to calculate a custom offset, such as six rows per output row.
What ranges does the first copied formula reference?
When the formula is entered in row 2, it references D5:D7 and E5:E7. The formula in the next output row references D11:D13 and E11:E13.
How can I change the formula to shift by a different number of rows?
Replace both instances of 6 in the formula with your required shift size. For example, use 4 to move to the next block four rows lower each time.
Can I use this formula if my output starts in a row other than row 2?
Yes. Replace A2 with the first output-row cell reference, and keep the second instance absolute, such as ROW($A$10), if the first formula is in row 10.




