In real life scenario, you may deal with different sum operations in Excel like sum with multiple criteria, sum entire column, sum every other column, sum every nth column, and many more. To help you out, this tutorial is set to show how to sum every nth column.

First things first, let’s get to know about our example workbook before starting the main article.

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 separated columns. Using this table we will see how to calculate the sum of every nth column.

Note that this is a basic table with mock data. In practical life, you may find a much larger and complex data set.

## Practice Workbook

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

## Sum nth Column

Sum every nth column usually refers to sum values after a regular interval. You set the interval, according to that number few values will be counted and few will not.

To show you examples we are adding two columns (*Number of Interval, Total)* separated from our table.

We will set the interval and find the sum based on that interval.

### 1. Using Formula

The formula, to sum every nth column, consists of several Excel functions. The functions are – **SUMPRODUCT, MOD, COLUMN. **

Each of the functions has its own purpose.

The **SUMPRODUCT **function returns the sum of the product between arrays or ranges.

The **MOD** function returns the remainder of two numbers after operating division

The** COLUMN** function returns the column number for a reference

Let’s see the formula now

`SUMPRODUCT((MOD(COLUMN(range)-COLUMN(range.firstCol)+1,interval)=0)*1,range)`

This formula takes the relative column reference using the **COLUMN** functions. The **MOD** function helps to generate an array of **TRUE** or **FALSE**.

The **SUMPRODUCT** uses the array as the argument returns the result.

Before divining further, write the formula in Excel.

We have set interval 2 ( we wanted to count every second value). Every value we have provided within the formula, using **Cell Reference**.

Let’s break down each of the portions.

Here the **COLUMN** function(s) were used to gain the relative column reference. Subtracting the first column from a range of columns returns relative column reference.

Write the **COLUMN** function part from our formula

And press the **F9 **key. You will see the result like the image below.

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.

Add 1 to the **COLUMN** functions and our relative column reference will start from 1.

The **COLUMN **function provides the relative column reference. Now let’s see the mechanism of the **MOD **function.

Write the **MOD **function portion, here our derived column reference is our array reference within the **MOD** function and the interval number is the divisor.

Press the **F9 **key. We will use the **F9 **key to see the insights of any formula.

You will see an array of remainders. These remainders are generated by dividing each of the elements of array reference by the divisor interval number.

Now make a logic check, compare the value to 0

Writing up to this press the **F9** key.

You will see an array of **TRUE** or **FALSE**. Which of the elements are 0 they return **TRUE**, **FALSE** otherwise.

Now multiply this with 1

This will return an array of 1s and 0s. For **TRUE** it will be 1 and for **FALSE** it will be 0.

Now the **SUMPRODUCT** function has an array of 1s and 0s, with this provide the column range you want to sum.

This function will multiply both the arrays, the values which will be multiplied with 1s, only they will be counted.

Then the function will task the sum operation and provide the result.

Let’s see the formula for the next row as well.

This time we have set the interval value to 3.

Write the formula for this row.

This sums every third number. If you have looked at the dataset, you will see our result was generated by summing the March value and the June value.

We can write the formula using the unary (-) operator. The generic formula will be something like this

`SUMPRODUCT(--(MOD(COLUMN(range)-COLUMN(range.firstCol)+1,interval)=0),range)`

These two unary operators `(--)`

ahead of the **MOD** function, convert the **TRUE** or **FALSE** array into an array of 1s and 0s.

To clarify, first write the portion in Excel

And press the **F9 **key.

We have found the array of 1s and 0s by this. Now we do not need to multiply the array produced by the **MOD **function with 1 anymore.

The rest of the procedures are the same.

We have shown you the formula applying on the row which results have already known to us.

Now write the formula for the next row.

We can write the formula with another approach as well.

So far we were separating the two arrays (**MOD** function generated array and sum range) using a comma.

That was giving the **SUMPRODUCT **function two arrays and the function itself multiply them before summing.

Now we will multiply the arrays of our own.

`SUMPRODUCT(--(MOD(COLUMN(range)-COLUMN(range.firstCol)+1, interval)=0)*range)`

Here * is the symbol of product operation. We are multiplying two arrays and providing the **SUMPRODUCT **function a single array.

Write the formula for a known result first.

The formula worked fine. We got the result we wanted.

Now using any of the formulas calculate the rest within the dataset.

### 2. Using VBA Code

You can do the task using VBA code also. You can define your own function in Excel and can perform operations using that.

From the **Developer** tab in Excel, you will find an option called **Visual Basic **

Click that. That will open the **Microsoft Visual Basic for Applications** window. Or you can use the keyboard shortcut **ALT + F11**.

Click **Insert** > **Module**, and paste the following code in the **Module** window

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

Save the code and use the declared function in Excel.

Here we have used the function and it gave us the result.

Do the same for the rests as well.

## Summing by Even or Odd

If you want to sum selecting the odd or even columns then you can do that using the formula we have discussed earlier. All you need to do is just a couple of small modifications.

The formula for summing the odd columns only will be

`SUMPRODUCT((MOD(COLUMN(range)-COLUMN(range.firstCol)+1,2)=1)*1,range)`

Here the interval will always 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 remainders whether 1 or not.

Since for odd columns, remainder will be 1 and checking that we will find which are the odd column there. Then sum them up using the **SUMPRODUCT **function.

For Even columns

`SUMPRODUCT((MOD(COLUMN(range)-COLUMN(range.firstCol)+1,2)=0)*1,range)`

Instead of 1, you need to check whether the remainder is 0 or not.

Write the formula in Excel.

Write the formulas for the respective fields and find the odd and even column total value for the example table.

Please keep in mind that you can write this **SUMPRODUCT** formula in all the other ways we have shown in the previous section.

## Conclusion

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