Sum every nth column

Learn how to sum every nth column (for example, every second, third, or fifth column) using the Excel SUMCOLUMNS function.

In this tutorial, we will demonstrate how the SUMCOLUMNS function works. Furthermore, we will perform the task using regular Excel functions. The SUMCOLUMNS function allows you to sum up a specific set of columns within a range of cells.

How to sum every nth column in Excel

Here is the formula to sum every nth column:

1. Enter SUMCOLUMNS(B3:J3,3, FALSE) formula.
2. Select the range of cells: B3:J3.
3. Add the interval (n) to control which columns will be summed.
4. Select the starting point, TRUE: first column, FALSE: “nth” column.
5. The function returns the sum of every nth column.

Syntax

=SUMCOLUMNS(range, n = interval, [start])

Arguments

The function uses two required and one optional argument.

• “range” is the range of cells where you search the values.
• “interval” is an integer (n) that identifies the column index.
• “start“: optional; the argument controls the starting column. In the case of TRUE, the function will start summing the columns from the first column; in the case of FALSE, every nth column will be summed.

SUMCOLUMNS Example

In the example, you want to sum every third column starting with the 3rd column. Use FALSE as a third [optional] argument to summarize the selected range’s 3rd, 6th, and 9th columns.

Formula:

=SUMCOLUMNS(B3:J3,3, FALSE)

Another formula with the TRUE argument:

=SUMCOLUMNS(B3:J3,3, TRUE)

In this case, the function will sum up the 1st, 4th, and 7th columns.

Implementation of the SUMCOLUMNS function:

Press Alt + F11 to open the VBA editor. Right-click and add a new module. Finally, copy and paste the following code into your Excel Workbook:

Function SUMCOLUMNS(rng As Range, n As Integer, Optional start As Boolean = True)
    Dim i As Integer
    Dim sum As Double
    sum = 0
    If start = True Then
        For i = 1 To rng.Columns.count Step n
            sum = sum + rng.Cells(1, i).Value
        Next i
    Else
        For i = n To rng.Columns.count Step n
            sum = sum + rng.Cells(1, i).Value
        Next i
    End If
    SUMCOLUMNS = sum
End Function

Now your function is ready to use.

Sum every nth column with built-in Excel functions

Let us see the classic way; it comes with a slightly longer formula based on the SUM and INDEX built-in functions. The goal is the same, sum every 3rd column using a formula.

The following formula starts summing from the first column in the selected range.

=SUM(INDEX(B3:J3,1,{1,4,7}))

The formula below starts summing from the nth column in the selected range.

=SUM(INDEX(B3:J3,1,{3,6,9}))

Related formulas:

• Get the nth value in a column
• Return the nth value in a row
• LOOKUP the nth value using NVLOOKUP

Additional resources:

• Excel Formulas