How to Sum Values in Fixed Groups of Every Nth Row in Excel
Question details
The user needs to group sequential data into sets of a fixed number (such as every 5 or 10 rows) and calculate the sum for each group.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating grouped summaries of sequential data (like age brackets or time periods) without having to manually shift the formula range for every new row.
- Observed behavior
- Accurately generating sums and corresponding labels for fixed groups of rows (e.g., 0-4, 5-9) using dynamic formulas or pivot tools.
Ensure your dataset is sorted in the correct sequential order, and check if your version of Excel supports dynamic array functions like WRAPROWS and SEQUENCE.
Use Dynamic Array Formulas (MMULT and WRAPROWS)
This is the most efficient method for modern spreadsheet versions, as it automatically structures the data into fixed rows and calculates the sum dynamically.
The WRAPROWS function takes a single column of data and wraps it into multiple columns based on your fixed group size (e.g., 5). The MMULT function then calculates the matrix product to sum these newly created rows.
Click on the cell where you want the first grouped sum to appear.
Type the formula =MMULT(WRAPROWS(B2:B101,5),{1;1;1;1;1}) and press Enter. Replace 'B2:B101' with your actual data range, and change '5' to your desired Nth row group size. The array {1;1;1;1;1} must match the group size.
In an adjacent column, use the formula =SEQUENCE(20,,0,5)&"-"&SEQUENCE(20,,4,5) to automatically create text labels like '0-4', '5-9', etc.
Create a PivotTable for Grouping Data
PivotTables are a highly maintainable and visual way to group data into fixed sequential sets without writing complex array formulas.
Use the GROUPBY and SCAN Functions (Advanced)
For highly customized grouping criteria, you can combine GROUPBY with SCAN to evaluate and sum rows based on conditions.
Easily Group and Sum Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced formulas, dynamic arrays, and comprehensive PivotTable features. You can effortlessly group values by every Nth row and summarize your datasets with complete accuracy.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your sequential dataset.
- 2. Apply the array formula: Select an empty cell and type =MMULT(WRAPROWS(B2:B101,5),{1;1;1;1;1}) to instantly calculate your fixed groups.
- 3. Use PivotTables as an alternative: Alternatively, go to Insert > PivotTable, drag your categories to the Rows field, right-click to Group by 5, and let WPS handle the math automatically.
Frequently Asked Questions
How do I group by every 10th row instead of 5?
If you are using the MMULT and WRAPROWS formula, change the second argument in WRAPROWS to 10, and expand the array in MMULT to include ten 1s: {1;1;1;1;1;1;1;1;1;1}. In a PivotTable, simply right-click the row labels, select Group, and change the 'By' value to 10.
Can I calculate the average instead of the sum for each Nth row group?
Yes. If using a PivotTable, right-click the values column, select 'Value Field Settings', and change the calculation from 'Sum' to 'Average'. If using formulas, it's easier to modify the WRAPROWS output by wrapping it with BYROW(WRAPROWS(B2:B101,5), LAMBDA(row, AVERAGE(row))).
Why does my MMULT formula return an error?
An error usually occurs if the number of columns created by WRAPROWS does not match the size of the array in MMULT. Ensure that if you group by 5, your MMULT array has exactly five 1s ({1;1;1;1;1}). Also, ensure your data range length is perfectly divisible by your group size, or WRAPROWS will pad the end with #N/A, causing MMULT to fail.
Is it possible to sum groups without sequential labels like ages?
Yes, the WRAPROWS and MMULT formula method does not rely on sequential labels; it strictly groups the data based on their physical row position (every 5 rows) in the selected range regardless of the label.
