How to sum every n rows down in Excel

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.

Syntax

=SUM_NROWS(range, n, k)

Arguments

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.

Example

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)

Explanation

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.

Formula:

=SUM(OFFSET($C$3,0,0,4,1))

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.

Sum every n rows