Automatically Calculate Regular-Interval Subtotals in Excel
Question details
Create formulas that calculate subtotals for groups of rows at a fixed recurring interval without manually entering each subtotal.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The worksheet contains a list of numeric values and requires a subtotal after every defined number of rows, such as every four rows.
- Observed behavior
- The user needs grouped subtotal results to update automatically when the values in each recurring row block change.
Identify the first cell in the value range and the number of rows in each group. The examples below assume values begin in B2 and each group contains four rows; adjust the cell references and group size for your worksheet.
Insert a subtotal formula after every four rows
Use this method when you want each subtotal displayed directly below its four-row group in the existing worksheet layout.
This formula calculates the sum of the four values immediately above the subtotal cell. Copy the repeating data-and-subtotal pattern down the worksheet to calculate additional groups.
Enter the first group of values in cells B2:B5.
Select cell B6 and enter =SUM(B2:B5), then press Enter.
Enter the next four values in B7:B10. In B11, enter =SUM(B7:B10). Continue this four-value-plus-one-subtotal pattern for additional groups.
If all rows contain values and you want the formula to show a subtotal only every fourth row, enter =IF(MOD(ROW()-ROW($B$2)+1,4)=0,SUM(INDEX($B:$B,ROW()-3):B2),"") in a results column beside B2, then fill it down.
Generate a spilled list of grouped subtotals with dynamic-array functions
Use this approach when you want a separate automatically spilled subtotal output instead of subtotal rows inserted into the source data.
Create recurring group subtotals in WPS Spreadsheets
WPS Spreadsheets lets you build recurring subtotal formulas with familiar Excel-style functions such as SUM, IF, MOD, ROW, and INDEX. Enter the formula once, fill it down, and the subtotal results recalculate as your source values change.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel .xlsx or .xls file containing your values.
- 2. Choose the results column: Click an empty cell beside the first value, such as C2 when values start in B2.
- 3. Enter the recurring subtotal formula: Type =IF(MOD(ROW()-ROW($B$2)+1,4)=0,SUM(INDEX($B:$B,ROW()-3):B2),"") and press Enter.
- 4. Fill the formula down: Select the formula cell and drag the fill handle down the results column to apply it to the remaining rows.
- 5. Change the interval when required: Edit the two instances of 4 in the formula to match the number of rows in each required group.
Frequently Asked Questions
How do I change the subtotal from every four rows to every five rows?
In the row-based formula, replace both instances of 4 with 5. For example: =IF(MOD(ROW()-ROW($B$2)+1,5)=0,SUM(INDEX($B:$B,ROW()-4):B2),""). The INDEX offset must be one less than the group size.
Why does the subtotal formula return blank cells between totals?
The IF function is designed to show a result only when the row reaches the selected interval. The empty-string argument "" returns blanks on all other rows so only the recurring subtotal rows display values.
Can I calculate recurring subtotals when my data begins in another row?
Yes. Replace $B$2 with the first cell of your actual value range. For example, if values begin in D10, use ROW($D$10) and update the B column references to D.
What should I do if WRAPROWS or BYROW is not recognized in Excel?
Those are newer dynamic-array functions and may not be available in your Excel version. Use standard formulas with SUM, IF, MOD, ROW, and INDEX instead, or open the workbook in a version that supports dynamic arrays.
Will the formula include blank cells in a four-row group?
SUM ignores blank cells, so the formula still returns the total of the numeric cells in that group. However, the subtotal position remains based on row count, not on the number of nonblank values.
