Sum Repeating Seven-Column Blocks in WPS Spreadsheets with OFFSET
Question details
The user needs a formula that sums horizontally repeating seven-column weekly blocks from a source sheet, where each copied formula advances to the next seven-column week instead of shifting by one column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Weekly values are stored horizontally in seven-column blocks, such as B2:H2, I2:O2, and so on, on a sheet named 'sales per day'. The user wants to copy a formula across a summary row so each cell totals the next weekly block.
- Observed behavior
- A normal copied SUM formula shifts by one column, so the copied formula does not sum the next seven-column block correctly.
Open the WPS Spreadsheets workbook and confirm the source worksheet, for example 'sales per day', contains data beginning at B2, with each week using seven columns such as B:H, I:O, and so on.
Use OFFSET with a Seven-Column Step to Sum Each Weekly Block
Use this when each copied formula should automatically jump to the next seven-column weekly block.
The OFFSET formula anchors at $B2 and moves 7 columns per copied cell. COLUMNS($A:A) returns 1 in the first formula, so the offset is 0; in the next copied cell it returns 2, giving an offset of 7, and so on.
The range is 1 row high and 7 columns wide, so each formula totals exactly one weekly block.
Select A2 on the summary sheet and enter: =SUM(OFFSET('sales per day'!$B2,0,(COLUMNS($A:A)-1)*7,1,7))
Drag the fill handle from A2 to the right. Each subsequent formula advances by seven columns: A2 sums B2:H2, B2 sums I2:O2, and so on.
Use a Fixed Absolute Range When Only the First Seven-Column Block Is Needed
Use this when you only need the first week's totals and do not need the range to advance when copied.
Sum Repeating Seven-Column Weekly Blocks with WPS Spreadsheets
WPS Spreadsheets supports the same SUM, OFFSET, and COLUMNS functions used in Excel, so you can build a formula that advances 7 columns per copied cell and totals each weekly block quickly.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the 'sales per day' source sheet, and select the summary worksheet where the weekly totals should appear.
- 2. Enter the OFFSET SUM formula: In A2, type =SUM(OFFSET('sales per day'!$B2,0,(COLUMNS($A:A)-1)*7,1,7)) and press Enter.
- 3. Copy the formula across for later weeks: Drag the fill handle to the right. WPS Spreadsheets advances the OFFSET by seven columns per cell, summing each weekly block.
Frequently Asked Questions
Why does a normal copied SUM formula fail with weekly seven-column blocks?
A normal SUM formula uses relative references that shift by one column when copied. Weekly data stored in seven-column blocks needs the formula to jump by seven columns, which you can do with OFFSET or INDEX.
How do I sum only the first week in WPS Spreadsheets?
Use a fixed absolute range: =SUM('sales per day'!$B2:$H2). This formula always totals B2:H2 and does not advance when copied.
Can I use this OFFSET formula if my data starts in a different column?
Yes. Change the anchor cell from $B2 to the first data column and keep the range width as 7. COLUMNS($A:A) controls the seven-column step for each copied formula.
Does WPS Spreadsheets support OFFSET and COLUMNS functions?
Yes. WPS Spreadsheets supports Excel-compatible functions including OFFSET, COLUMNS, and SUM, so you can use the same formula logic in WPS Office.
