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