# Sum every n rows

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

1. Enter SUMN_ROWS(C3:C14, 4,2) function
2. Select the range: C3:C14
3. Add how many cells you want to summarize (4)
4. Set which group of n rows will calculate (2)
5. 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.

### Related Formulas

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.