How to Sum Every Group of Seven Rows in Excel
Question details
The user needs an Excel formula that sums every consecutive group of seven rows and can be copied down a worksheet without manually adjusting each range.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has data starting at BM2 and wants each formula row to total the next block of seven rows.
- Observed behavior
- A normal SUM formula copied down moves one row at a time, so it sums overlapping seven-row ranges instead of distinct groups.
Identify the first data cell and the column you want to sum. The example starts at BM2, so if your data starts in a different cell or column, adjust the formula's starting reference before copying it.
Use SUM and OFFSET to total every seven rows
Use this solution when you want each copied formula to start exactly seven rows below the previous formula.
The formula =SUM(OFFSET(BM2,(ROW()-2)*7,0,7,1)) starts at BM2 and uses ROW() to calculate a zero-based row offset of seven rows for each successive formula row.
Click the cell where the first seven-row total should appear, normally in an adjacent summary column.
Type =SUM(OFFSET(BM2,(ROW()-2)*7,0,7,1)) and press Enter. This sums BM2:BM8.
Drag the fill handle down the column. Each row now totals the next block of seven cells, such as BM9:BM15.
Sum Consecutive 7-Row Groups with WPS Office Spreadsheets
Use WPS Office Spreadsheets to enter the same SUM and OFFSET formula as Excel, then drag it down to calculate totals for every group of seven rows.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the Excel file that contains the data you want to total.
- 2. Select the summary cell: Click the cell where the first seven-row total should appear.
- 3. Type the grouped sum formula: Enter =SUM(OFFSET(BM2,(ROW()-2)*7,0,7,1)) and press Enter.
- 4. Fill the formula down: Drag the fill handle down the column to create totals for all subsequent seven-row groups.
- 5. Save in Excel format: Press Ctrl+S and choose .xlsx to keep full Excel compatibility.
Frequently Asked Questions
What does the OFFSET formula do in this seven-row sum?
OFFSET starts at BM2, moves down by (ROW()-2)*7 rows, and returns a seven-row range for SUM to total.
Why does the formula use ROW()-2?
It converts the current formula row into a zero-based offset. If the first formula is in row 2, the offset is 0; the next formula row gives offset 7.
How do I change the formula for groups of five or ten rows?
Replace the 7 in the OFFSET formula's height argument and multiplier with the group size, for example =SUM(OFFSET(BM2,(ROW()-2)*5,0,5,1)).
Can I use this formula in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the same SUM and OFFSET functions and can open and save .xlsx workbooks.
What if my data starts in row 2 but in column B?
Change BM2 to B2 in the formula. The rest of the formula stays the same.
