Learn how to sum every n rows in Excel using the SUMN_ROWS function or regular (SUM and OFFSET) functions.
How to sum every n rows in Excel
- Enter SUMN_ROWS(C3:C14, 4,2) function
- Select the range: C3:C14
- Add how many cells you want to summarize (4)
- Set which group of n rows will calculate (2)
- The function returns the sum of every n rows
The point is that you specify the group of cells you want to sum. You also add two arguments, n and k, and both numbers are integers. The first argument (n) controls the number of rows the SUMN_ROWS will use – the third argument (k) control which group of n rows will summarize.
=SUM_NROWS(range, n, k)
The function uses three required arguments:
- Range: specify the group of cells you want to sum.
- n: the number of rows the function will summarize.
- k: the number group of n rows will calculate.
In the example, you want to sum every n rows. Take a look at the picture below! First, select the range C3:C14 and add a descriptive name for a range, for example, “Sales”. You can add a name to a range by clicking the name box.
If you want to sum the sales between Week 5 and Week 8, use the following formula:
=SUMN_ROWS(Sales, 4, 2)
First, you need to add the range. Select cell C3:C14. We find a group that contains 4 records, so add “4” as a second argument. To get the sum of Sales between the 4th and 8th week, you need to use the second group, C7:C10; the third argument is “2”.
=SUMN_ROWS(Sales, 4, 2) =SUM(C7:C10) = 4000 + 5000 + 10000 + 4000 =23000
Insert the following code into your Workbook:
Function SUMN_COLUMNS(rng As Range, n As Integer, k As Integer) As Double SUMN_COLUMNS = Application.WorksheetFunction.sum(rng.Columns _ ((k - 1) * n + 1 & ":" & k * n).SpecialCells(xlCellTypeConstants)) End Function
SUM every n rows with OFFSET and SUM functions
If you are unfamiliar with user-defined functions, you can sum every n rows using regular Excel functions, like OFFSET and SUM.
Good to know that OFFSET refers to a range: n rows x n columns, so use the following setup for the arguments:
- row_num = 0, the reference will start from the same row as the starting cell (C3).
- col_num = 0; the reference will start from the same column as the starting cell (C3).
- height = 4, the function will include 4 rows (in the example, the first 4 values)
- width = 1, we will include a single column
Finally, the SUM function will summarize the values in the selected range.