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:
- Enter SUMCOLUMNS(B3:J3,3, FALSE) formula.
- Select the range of cells: B3:J3.
- Add the interval (n) to control which columns will be summed.
- Select the starting point, TRUE: first column, FALSE: “nth” column.
- The function returns the sum of every nth column.
=SUMCOLUMNS(range, n = interval, [start])
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.
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.
Another formula with the TRUE argument:
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.
The formula below starts summing from the nth column in the selected range.