# How to sum every N rows in Excel (groups of rows)

Number the blocks with INT and add the rows in each: `=SUMPRODUCT((INT((ROW($B$2:$B$16)-ROW($B$2))/$E$1)=D3-1)*$B$2:$B$16)`.

**Excel:** `=SUMPRODUCT((INT((ROW($B$2:$B$16)-ROW($B$2))/$E$1)=D3-1)*$B$2:$B$16)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Day | Calls |  | Rows per group | 5 |
| Mon | 42 |  | Week | Calls |
| Tue | 38 |  | 1 |  |
| Wed | 51 |  | 2 |  |
| Thu | 47 |  | 3 |  |
| Fri | 55 |  |  |  |
| Mon | 40 |  |  |  |
| Tue | 44 |  |  |  |
| Wed | 49 |  |  |  |
| Thu | 53 |  |  |  |
| Fri | 58 |  |  |  |
| Mon | 39 |  |  |  |
| Tue | 41 |  |  |  |
| Wed | 46 |  |  |  |
| Thu | 50 |  |  |  |
| Fri | 61 |  |  |  |

Formula in E3, filled to E5: `=SUMPRODUCT((INT((ROW($B$2:$B$16)-ROW($B$2))/$E$1)=D3-1)*$B$2:$B$16)`

Result: 233; 244; 237

## How it works

`ROW(…)-ROW($B$2)` numbers the rows 0 to 14, and dividing by 5 with INT turns that into block numbers: 0 for the first five rows, 1 for the next five, 2 for the last. Each summary row keeps the block equal to its week minus 1 and adds those calls. Change E1 to group by any size.

## Other ways

- `=SUM(TAKE(DROP($B$2:$B$16,(D3-1)*$E$1),$E$1))`: Excel 365: skip the earlier blocks with DROP, keep the next block with TAKE.
- `=SUM(CHOOSEROWS($B$2:$B$16,SEQUENCE($E$1,1,(D3-1)*$E$1+1)))`: Excel 365 and Google Sheets: CHOOSEROWS picks the rows of the block by position.

## Common mistakes

- This counts rows, not dates. If a day is missing, every later week shifts. With real dates, sum by week with SUMIFS instead.
- Every block must be the same size. A partial last block is summed as a smaller group.

## Questions

**How do I put the block total on the last row of each block instead?**

In Excel 365, fill `=IF(MOD(ROWS($B$2:B2),$E$1)=0,SUM(TAKE($B$2:B2,-$E$1)),"")` down next to the data: every fifth row shows the total of the five rows ending there.

**How do I sum every nth row instead of blocks?**

Use MOD on the position: `=SUMPRODUCT((MOD(ROW(B2:B16)-ROW(B2)+1,5)=0)*B2:B16)` adds rows 5, 10 and 15.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-every-n-rows