Formula Errors

Shift Excel Formula References by Six Rows When Copying Down

Jason MillerJason Miller Nov 12, 2026 868 views

Question details

Create a formula that can be copied down while shifting its source ranges by six rows for each result row.

Shift Excel Formula References by Six Rows When Copying Down
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the first output cell

Click the cell in row 2 where you want the first result to appear, such as A2 or your chosen results column.

2
Enter the shifting SUMPRODUCT formula

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.

3
Copy the formula downward

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.

4
Verify the next referenced block

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.

Use INDEX and ROW to move each source block down six rows
Six-row reference shift applied: With the formula starting in row 2, every copied row advances the referenced three-row ranges by six rows.
Calculate stepped reference blocks in WPS Spreadsheets

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. 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open your Excel workbook from the File menu.
  2. 2. Choose the first result cell: Click the first cell for the output, such as A2, where the formula should begin.
  3. 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. 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.
Use INDEX and ROW to advance each calculation block by six rows automatically.Calculate matching values in columns D and E with SUMPRODUCT.Open, edit, and save Microsoft Excel .xlsx and .xls workbooks.Use a familiar spreadsheet interface for quick migration from Excel.
microsoft office alternative - wps office

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.