Sum Every nth Column (Formula and VBA Code)

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.

Excel sheet - Sum Every nth Column

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.

New columns - Sum Every nth Column

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)

Formula - Sum Every nth Column

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.

Result - Sum Every nth Column

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

COLUMN - Sum Every nth Column

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

Insights - Sum Every nth Column

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.

Column Reference - Sum Every nth Column

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

Column Reference result - Sum Every nth Column

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.

MOD - Sum Every nth Column

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

Insights MOD-Sum Every nth ColumnYou 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

MOD check - Sum Every nth Column

Writing up to this press the F9 key.

TRUE - FALSE array -Sum Every nth Column

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

Product with 1 -Sum Every nth Column

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

Multiple 1 Result-Sum Every nth Column

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.

Result of formula - Sum Every nth Column

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

This time we have set the interval value to 3.

new example - Sum Every nth Column

Write the formula for this row.

Example 2 - Sum Every nth Column

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)

Formula with unary operator- Sum Every nth Column

These two unary operators (--) ahead of the MOD function, convert the TRUE or FALSE array into an array of 1s and 0s.

Result for Unary operators formula - Sum Every nth Column

To clarify, first write the portion in Excel

Unary Mechanism - Sum Every nth Column

And press the F9 key.

Result unary-Sum Every nth Column

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.

Example 2 for multiply array -Sum Every nth Column

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)

Multiply arrays - Sum Every nth Column

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.

Result for multiple arrays-Sum Every nth Column

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

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

All example - Sum Every nth Column

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 

Visual Basic -Sum Every nth Column

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.

SUMINTerval- Sum Every nth Column

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

Do the same for the rests as well.

Fill all - Sum Every nth Column

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.

ODD- Sum Every nth Column

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.

EVEN columns sum- Sum Every nth Column

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

SUM even or odd columns -Sum Every nth Column

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.

shakil

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

ExcelDemy
Logo