How to Sum Every Fourth Row in WPS Office Excel While Excluding Total Rows
Question details
The user needs a formula to sum four staggered row series in Excel while excluding grand-total rows that repeat every 229 rows, with data starting at E7 and the first total row at 235.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A workbook has values starting in E7 and includes grand-total rows at row 235 and every 229 rows after that. The user wants to sum each of the four staggered every-fourth-row series without including the total rows.
- Observed behavior
- A regular SUM or basic every-fourth-row formula risks including the grand-total rows, so a conditional SUMPRODUCT formula with MOD is needed.
Confirm that your data starts at E7, the first grand-total row is 235, and totals repeat every 229 rows. Note the actual last row with data so you can replace 16006 in the example formula.
Sum the first every-fourth-row series with SUMPRODUCT and MOD
Use this formula when you want the total for rows 7, 11, 15, and so on while automatically skipping grand-total rows 235, 464, and 693.
The formula uses ROW() to evaluate each position in the range, MOD(ROW()-7,4)=0 to isolate every fourth row starting at E7, and MOD(ROW()-235,229)<>0 to exclude the total rows. N() converts the remaining cells to numeric values for SUMPRODUCT.
Click an empty cell where you want the first series total to appear.
Type or paste =SUMPRODUCT((MOD(ROW($E$7:$E$16006)-235,229)<>0)*(MOD(ROW($E$7:$E$16006)-7,4)=0)*N($E$7:$E$16006)) into the formula bar and press Enter.
Change 16006 in both $E$7:$E$16006 references to the actual last row that contains values, such as $E$7:$E$20000.
Spot-check by temporarily filtering rows 7, 11, 15, and a known total row such as 235 to make sure the total rows are not counted.
Create the other three staggered series totals
Use this after the first formula to sum rows offset by 1, 2, and 3 from the starting row.
Sum Staggered Fourth Rows in WPS Office Excel
WPS Office Excel supports the same SUMPRODUCT, MOD, ROW, and N functions, so you can apply this conditional sum directly in WPS Office and safely exclude grand-total rows without changing your workbook.
- 1. Download and open WPS Office: Go to the official WPS Office download page, install WPS Office, and open your Excel workbook in WPS Office Spreadsheets.
- 2. Select a blank result cell: Click an empty cell where you want the first series total to appear.
- 3. Enter the first series formula: Type =SUMPRODUCT((MOD(ROW($E$7:$E$16006)-235,229)<>0)*(MOD(ROW($E$7:$E$16006)-7,4)=0)*N($E$7:$E$16006)) and press Enter.
- 4. Adjust the range and create the four series formulas: Change 16006 to your actual last row, then copy the formula and change the final MOD comparison to 1, 2, and 3 for the other three series.
- 5. Save your workbook: Press Ctrl+S and choose .xlsx to keep full compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the formula exclude row 235 instead of row 229?
Because the grand-total rows begin at row 235 in this layout. The expression MOD(ROW()-235,229)<>0 excludes 235, 464, 693, and every 229th row after 235, which prevents those totals from being included.
How do I get the totals for rows 8, 12, 16 and the other staggered sequences?
Copy the first formula and change the final comparison from =0 to =1, =2, and =3. These values select the rows offset by one, two, and three positions from the starting row E7.
Can I use this formula if my totals are spaced differently or start on another row?
Yes. Replace 229 with your actual total-row interval and replace 235 with the first total row. Also adjust the -7 value to match your actual starting row.
Will this formula work if my data is in another column or begins at a different row?
Yes. Change the range from $E$7:$E$16006 to your actual range, and update both MOD ROW offsets so they match the starting row and first total row of your data.
Does WPS Office support SUMPRODUCT and MOD formulas?
Yes. WPS Office Spreadsheets supports SUMPRODUCT, MOD, ROW, and N functions, so you can use the same formula and save the workbook as .xlsx for Microsoft Excel compatibility.
