How to Sum Every Nth Column in Excel (3 Methods)

We have a sample table of Products and revenue of the items for each month from January to June. The revenues for each month are stored in separate columns. Sum every nth column usually refers to sum values after a regular interval. You set the interval, and according to that number, a few values will be counted and a few more will not. To show you examples, we are adding two columns (Number of Intervals, Total) separated from our table. We will set the interval and find the sum based on that interval. To do that, we will apply the SUMPRODUCT, MOD, SUM, FILTER, SEQUENCE and COLUMNS functions as well as the VBA code.

Method 1 – Combine SUMPRODUCT, MOD, and COLUMN Functions to Sum Every Nth Column

Steps:

Select cell K5 and enter the following SUMPRODUCT, MOD and COLUMN functions in that cell.

=SUMPRODUCT((MOD(COLUMN(C5:H5)-COLUMN(C5)+1,J5)=0)*1,C5:H5)

Formula Breakdown:

Inside the MOD function, J5 is the column interval., and COLUMNS(C5:H5) returns column numbers which are 6. The MOD function returns a numeric array. The array is,

{1,2,3,4,5,6}

The MOD function returns the remainder of each value which is divided by 2 in the array.
Since we have subtracted the first column from our column range, the array starts from 0.
As our counting starts from 1, we need to add 1 to it
When the output of MOD is compared to zero, an array of TRUE and FALSE values is produced. The return is,

{FALSE, TRUE, FALSE, TRUE, FALSE, TRUE}

The second value is always TRUE. We wish to sum up these values.
The SUMPRODUCT function returns the sum of the product between arrays or ranges.

Press Enter.

You will get the sum of every second column which is the return of the SUMPRODUCT, MOD and COLUMN. The return is $3,000.00.

Use the Autofill Handle for the remaining cells in column

Read More: How to Sum Entire Column in Excel

Method 2 – Merge SUM, FILTER, SEQUENCE, and COLUMNS Functions

Steps:

Enter the following SUM, FILTER, SEQUENCE and COLUMNS functions in cell K5.

=SUM(FILTER(C5:H5,MOD(SEQUENCE(1,COLUMNS(C5:H5)),J5)=0))

Formula Breakdown:

Inside the SEQUENCE function, 1 is the row number, and COLUMNS(C5:H5) returns column numbers which are 6. The SEQUENCE function returns a numeric array. The array is,

{1,2,3,4,5,6}

The MOD function returns the remainder of each value which is divided by 3 in the array.
When the output of MOD is compared to zero, an array of TRUE and FALSE values is produced. The return is,

{FALSE, FALSE, TRUE, FALSE, FALSE, TRUE}

The third value is always TRUE. We wish to sum up these values.
The FILTER function will filter the TRUE value.
The SUM function will sum up the TRUE value.

Press Enter.

You will get the sum of every second column which is the return of the SUM, FILTER, SEQUENCE and COLUMNS. The return is $1,700.00.

AutoFill the SUM, FILTER, SEQUENCE, and COLUMNS functions to the rest of the cells in column K.

Method 3 – Using VBA Code to Sum Every Nth Column

Step 1:

To open a Module, from your Developer tab, go to,

Developer → Visual Basic

After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will open. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

In the module, enter the following VBA code-

Function SUMINTERVAL(WorkRng As Range, interval As Integer) As Double
Dim arr As Variant
Dim total As Double
total = 0
arr = WorkRng.Value
For j = interval To UBound(arr, 2) Step interval
total = total + arr(1, j)
Next
SUMINTERVAL = total
End Function

Run the VBA.

Run → Run Sub/UserForm

Save that code with the .xlsm extension using Ctrl + S keyboard shortcut.

Go back to your worksheet, select cell K5 and enter the following user-defined function which is created by VBA. The user-defined function is,

=SUMINTERVAL(C5:H5,J5)

Use the Autofill Handle tool for the remaining cells in column K.

Summing by Even or Odd

If you want to sum either the odd or even columns, you can use the formula we discussed earlier with just a couple of small adjustments.

Step 1:

The formula for summing only the odd columns is,

=SUMPRODUCT((MOD(COLUMN(C5:H5)-COLUMN(C5)+1,2)=1)*1,C5:H5)

The interval will always be 2. Since we can understand whether a number is even or odd by its remainder divided by 2.
In the calculation procedure inside the MOD function, it gives an array of remainders. We have checked whether the remainder is 1 or not.
The remainder will be 1 for odd columns, so by checking that, we will find which are the odd columns. Sum them up using the SUMPRODUCT function.