How to Copy a Formula Down in WPS Spreadsheets While Increasing the Source Row by Seven
Question details
The user needs a formula in WPS Spreadsheets that can be filled down from C11 and that changes the COUNTIF source row from 97 to 104, 111, and subsequent rows in seven-row increments.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is entering a COUNTIF formula in C11 and copying it downward while the source range must move from C97:Z97 to C104:Z104, C111:Z111, and so on.
- Observed behavior
- A normal fill-down does not create the required seven-row source-row jump; the formula needs a calculated row offset.
Confirm that the source data on Sheet_1 is laid out in regular blocks starting at row 97, and that C11 is the first cell where the formula will be entered. The formula uses C11 as the anchor, so beginning in a different cell changes the calculated offset.
Use an OFFSET Formula to Increase the Source Row by Seven
Use this when you want a direct copy-down formula that shifts the COUNTIF range from C97:Z97 to C104:Z104, C111:Z111, and following blocks.
The formula uses (ROW()-ROW($C$11))*7 to calculate the offset. In C11 the offset is 0, in C12 it is 7, in C13 it is 14, and so on. OFFSET then moves the starting cell down by that many rows while keeping the 1-row-by-24-column range.
Click cell C11 in the destination sheet, type =COUNTIF(OFFSET('Sheet_1'!$C$97,(ROW()-ROW($C$11))*7,0,1,24),1), and press Enter.
Drag the fill handle from cell C11 down through the required rows, or copy cell C11 with Ctrl+C and paste it into the cells below with Ctrl+V.
Use an INDEX Formula as a Non-Volatile Alternative
Use this if you want the same seven-row increment without relying on the volatile OFFSET function.
Fill a Formula Down in WPS Spreadsheets with a Seven-Row Source Increment
Open the same workbook in WPS Spreadsheets, enter the OFFSET COUNTIF formula in C11, and copy it down. WPS Spreadsheets handles the same formula functions and fill behavior while keeping the file compatible with Microsoft Excel .xlsx format.
- 1. Open the workbook and select C11: Launch WPS Office, open the workbook containing Sheet_1, and click cell C11 on the destination sheet.
- 2. Enter the OFFSET COUNTIF formula: Type =COUNTIF(OFFSET('Sheet_1'!$C$97,(ROW()-ROW($C$11))*7,0,1,24),1) and press Enter.
- 3. Copy the formula down: Drag the fill handle from C11 downward, or press Ctrl+C on C11, select the target cells, and press Ctrl+V.
- 4. Verify the seven-row pattern: Confirm that C11 evaluates C97:Z97, C12 evaluates C104:Z104, C13 evaluates C111:Z111, and the pattern continues.
Frequently Asked Questions
Why does a normal fill-down only increment my source reference by one row?
A normal fill-down increments any relative row reference by one. To jump from C97 to C104, C111, and so on, the formula must multiply the destination row difference by seven, as in (ROW()-ROW($C$11))*7.
Can I change the starting row or the number of rows to skip?
Yes. Replace the starting row 97 with your actual starting row and replace the 7 with your desired block size. Keep the formula in C11 so the starting offset remains 0.
What does the 1,24 at the end of the OFFSET formula mean?
The 1 and 24 define the returned range size: one row tall and 24 columns wide. Starting at C97, this gives the range C97:Z97; when offset by seven rows, it gives C104:Z104, C111:Z111, and so on.
Does this formula work in WPS Spreadsheets and Microsoft Excel?
Yes. OFFSET, ROW, and COUNTIF are supported in both WPS Spreadsheets and Microsoft Excel, and the formula can be saved in .xlsx files without conversion.
