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.

**Table of Contents**hide

## 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.

### 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.

- 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.**

**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**

- 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**

**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.

- 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)`

- 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.

## 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.