Autofill Excel Weekly SUM Formulas With Source Ranges Increasing by Seven Rows
Question details
Create a spilling list of weekly SUM totals where each formula sums a seven-row source range and the next range starts seven rows later.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to calculate recurring weekly totals from column B on another worksheet without manually editing each source range.
- Observed behavior
- Each generated total should sum seven rows beginning at row 4, then advance to the next seven-row block automatically.
Confirm that the source worksheet name is correct and that column B contains the values to total. Use a version of Excel that supports dynamic-array functions, including SEQUENCE, MAP, and LAMBDA.
Use MAP, SEQUENCE, and INDIRECT to spill weekly SUM totals
Use this formula when every result should total a seven-row block in the source sheet and each subsequent block should begin seven rows later.
SEQUENCE generates starting row numbers of 4, 11, 18, and so on. MAP passes each starting row to LAMBDA, while INDIRECT builds the corresponding seven-row reference in the DAILY INPUT worksheet.
Click the cell where the first weekly total should appear, such as D4. Make sure the cells below it are empty so the results can spill.
Enter =MAP(SEQUENCE(COUNTA(A4:A1000),1,4,7),LAMBDA(startRow,SUM(INDIRECT("'DAILY INPUT'!B"&startRow&":B"&(startRow+6))))) and press Enter.
Replace 'DAILY INPUT' with your exact worksheet name, change B to the source column you need, and replace 4 with the first row of the first seven-row block.
Check that the first result sums DAILY INPUT!B4:B10, the next result sums B11:B17, and each following result advances by seven rows.
Create Seven-Row Weekly SUM Calculations in WPS Spreadsheet
WPS Spreadsheet provides a familiar worksheet environment for building weekly totals, filling formulas, and managing Excel workbooks. You can enter a weekly SUM formula, copy it down with a seven-row reference pattern, and save the completed file in Excel-compatible format.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the DAILY INPUT worksheet.
- 2. Choose the first results cell: Click the first cell for weekly totals, such as D4, and confirm that the output cells below are available.
- 3. Enter the weekly calculation: Type the required SUM formula or supported dynamic-array formula in the formula bar, using the DAILY INPUT sheet name and the first seven-row range.
- 4. Fill or extend the calculation: Use the fill handle from the selected cell to copy the calculation down, then adjust row references so each new total uses the next seven-row block.
- 5. Save in Excel format: Select Menu > Save As and choose Excel Workbook (*.xlsx) to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
What does SEQUENCE(COUNTA(A4:A1000),1,4,7) return?
It creates a vertical list of starting row numbers. The list begins at 4 and increases by 7 each time, producing 4, 11, 18, and subsequent weekly starting rows.
Why is INDIRECT used in the weekly SUM formula?
INDIRECT converts the text reference built from the worksheet name, column letter, and row numbers into an actual range. For example, it turns 'DAILY INPUT'!B4:B10 into a range that SUM can calculate.
How can I change the formula to total a different column?
Replace both instances of B in the INDIRECT text with the required column letter. For example, use "'DAILY INPUT'!C" to total seven-row ranges in column C.
How do I start the first weekly range on a different row?
Change the 4 in the SEQUENCE function to your desired first row. If the first range should be B20:B26, use SEQUENCE(COUNTA(A4:A1000),1,20,7).
Why does the formula show a spill error?
A #SPILL! error means one or more cells in the intended output range are not empty. Clear or move the blocking cells below the formula cell, then recalculate.
