Sum Every Nth Row In Excel With Ease

Summing every nth row in Excel can be a bit tricky, but don't worry, we've got you covered. In this article, we'll show you how to do it with ease using various methods. Whether you're a beginner or an advanced user, you'll find a solution that suits your needs.

Why Sum Every Nth Row?

Summing every nth row can be useful in various scenarios, such as:

• Analyzing data that occurs at regular intervals, like sales figures every 5th day
• Calculating totals for specific periods, like weekly or monthly totals
• Simplifying large datasets by summarizing data at regular intervals

Method 1: Using the SUM Function with OFFSET

One way to sum every nth row is by using the SUM function in combination with the OFFSET function. Here's the formula:

=SUM(OFFSET(A1,(ROW()-1)*n,0,n,1))

Assuming you want to sum every nth row in column A, starting from row 1, and n is the interval (e.g., 5).

• ROW()-1 returns the relative row number ( starting from 0)
• (ROW()-1)*n calculates the row offset
• OFFSET returns a range of cells starting from A1, offset by the calculated row, and spanning n rows
• SUM calculates the total of the returned range

Example

Suppose you have data in column A, and you want to sum every 5th row, starting from row 1.

Row	Data
1	10
2	20
3	30
4	40
5	50
6	60
7	70
8	80
9	90
10	100

Enter the formula =SUM(OFFSET(A1,(ROW()-1)*5,0,5,1)) in cell B1, and copy it down to sum every 5th row.

Method 2: Using Power Query

If you're using Excel 2010 or later, you can use Power Query to sum every nth row. Here's how:

1. Select the data range (e.g., A1:A10)
2. Go to the Data tab > From Other Sources > From Microsoft Query
3. In the Query Editor, go to the Home tab > Group By > Group By
4. Select the column you want to sum (e.g., Data)
5. Set the Group By interval to n (e.g., 5)
6. Click OK
7. Load the query back into Excel

The resulting table will have the summed values for every nth row.

Example

Using the same data as before, follow the steps to create a Power Query that sums every 5th row.

The resulting table will be:

Row	Sum
1	50
6	150
11	250

Method 3: Using VBA Macro

If you're comfortable with VBA, you can create a macro to sum every nth row. Here's the code:

Sub SumEveryNthRow()
    Dim n As Integer
    Dim lastRow As Long
    Dim sumRange As Range
    Dim i As Long
    
    n = InputBox("Enter the interval (n):")
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To lastRow Step n
        Set sumRange = Range(Cells(i, 1), Cells(i + n - 1, 1))
        Cells(i, 2).Value = Application.Sum(sumRange)
    Next i
End Sub

This macro prompts you to enter the interval (n), then sums every nth row and outputs the results in the adjacent column.

Example

Using the same data as before, run the macro and enter 5 as the interval.

The resulting output will be:

Row	Data	Sum
1	10	50
2	20
3	30
4	40
5	50
6	60	150
7	70
8	80
9	90
10	100

Excel Formula Gallery

Now that you've learned how to sum every nth row in Excel using various methods, try experimenting with different intervals and data ranges. Don't hesitate to ask if you have any questions or need further clarification.