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

Get FREE Advanced Excel Exercises with Solutions!

In real-life scenarios, you may deal with different sum operations in Excel, like the sum with multiple criteria, the sum entire column, the sum of every other column, the sum of every nth column, and many more. To help you out, this tutorial is set to show how to sum every nth column. Microsoft Excel offers you some functions to do that.


Sum Every Nth Column in Excel: 3 Suitable Ways

We have a 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, and the VBA code as well.

sum every nth column


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

In this section, we will apply the SUMPRODUCT, MOD, and COLUMN functions to sum every nth column in Excel. Firstly, we will sum up the sales every second month. Hence, we will sum up the sales in the interval we want. From our dataset, we can easily do that. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cell K5 and write down the below SUMPRODUCT, MOD, and COLUMN functions in that cell. The functions are,
=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}

  • Notably, 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.

Combine SUMPRODUCT, MOD, and COLUMN Functions

  • Hence, simply press Enter on your keyboard. As a result, you will get the sum of every second column which is the return of the SUMPRODUCT, MOD, and COLUMN functions. The return is $3,000.00.

  • Further, AutoFill the SUMPRODUCT, MOD, and COLUMN functions to the rest of the cells in column K.

Merge SUM, FILTER, SEQUENCE, and COLUMNS Functions

Read More: How to Sum Entire Column in Excel


2. Merge SUM, FILTER, SEQUENCE, and COLUMNS Functions

Now, we will use the SUM, FILTER, SEQUENCE, and COLUMNS functions to sum every nth column in Excel. From our dataset, we can easily do that. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cell K5 and write down the below SUM, FILTER, SEQUENCE, and COLUMNS functions in that cell. The functions are,
=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}

  • Notably, the third value is always TRUE. We wish to sum up these values.
  • The FILTER function will filter the TRUE value.
  • Finally, the SUM function will sum up the TRUE value.
  • Hence, simply press Enter on your keyboard. As a result, you will get the sum of every second column which is the return of the SUM, FILTER, SEQUENCE, and COLUMNS functions. The return is $1,700.00.

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


3. Using VBA Code to Sum Every Nth Column

Now I’ll show you how to sum every nth column in Excel by using a simple VBA code. You can define your function in Excel and perform operations using it. It’s very helpful for some particular moments. From our dataset, we will sum every nth column in Excel. Let’s follow the instructions below to learn!

Step 1:

  • First of all, open a Module, to do that, from your Developer tab, go to,

Developer → Visual Basic

Using VBA Code to Sum Every Nth Column

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Using VBA Code to Sum Every Nth Column

Step 2:

  • Hence, the Sum Every nth Column module pops up. In the  module, write down the below 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

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • Further, save that code with the  .xlsm  extension using  Ctrl + S  keyboard shortcut.

Using VBA Code to Sum Every Nth Column

  • After that, go back to your worksheet select cell K5, and write down the below user-defined function which is created by VBA The user-defined function is,
=SUMINTERVAL(C5:H5,J5)

Using VBA Code to Sum Every Nth Column

  • Further, AutoFill the SUMINTERVAL function to the rest of the cells in column K.
  • You will be able to sum up every second column which has been given in the below screenshot.

Using VBA Code to Sum Every Nth Column


Summing by Even or Odd

If you want to sum by selecting the odd or even columns, then you can do that using the formula we discussed earlier. All you need to do is just a couple of small modifications. Let’s follow the instructions below to learn!

Step 1:

  • The formula for summing the odd columns only will be,
=SUMPRODUCT((MOD(COLUMN(C5:H5)-COLUMN(C5)+1,2)=1)*1,C5:H5)
  • Here the interval will always be 2. Since we can understand whether a number is even or odd by its remainder divided by 2.
  • You might have remembered the calculation procedure inside the MOD function, it gives an array of remainders. We have checked the remainder whether 1 or not.
  • Since for odd columns, the remainder will be 1 and checking that we will find which are the odd columns Then sum them up using the SUMPRODUCT function.

Step 2:

  • The formula for summing the Even columns only will be,
=SUMPRODUCT((MOD(COLUMN(C5:H5)-COLUMN(C5)+1,2)=0)*1,C5:H5)
  • Instead of 1, you need to check whether the remainder is 0 or not.

Read More: How to Total a Column in Excel


Bottom Line

👉 You can pop up the Microsoft Visual Basic for Applications window by pressing  Alt + F11  simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

👉 #N/A! the error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! the error happens when a value is divided by zero(0) or the cell reference is blank.


Download Practice Workbook

You are welcome to download the workbook from the link below.


Conclusion

That’s all for the article today. We have tried listing several ways to sum every nth column. I hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know of any other formulas or methods that we might have missed here.


Related Articles


<< Go Back to Sum Columns | Sum in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo