Sometimes for data listed in rows, we may want to sum data every Nth column, for example sum data only in ODD column or EVEN column (every 2 column). In our daily life, we may meet many cases like this. It is necessary for us to have the knowledge of sum data by every Nth column in excel. Actually, excel built-in functions SUMPRODUCT, MOD, and COLUMN can help us resolve this issue properly.

This article will show you ‘to sum every Nth column’ based on SUMPRODUCT, MOD and COLUMN functions. MOD function is frequently used in sum every Nth case, COLUMN returns column number for a reference. Thus, SUMPRODUCT function is used for ‘sum’, the combination of MOD and COLUMN works on locate ‘every Nth column’. We will introduce above three functions with simple examples, descriptions, screenshots and explanations in this article, and also show you the usage of them. Finally, we will let you know the formula workflow step by step clearly.

After reading the following article, I’m sure you can have a simple understanding of SUMPRODUCT, MOD and COLUMN functions. Besides, you can learn well on sum data every Nth column/row. I’m sure you can work well with these functions in your daily work in the future.

EXAMPLE:

We prepare three lists and set ‘N’ value in J column. N means to sum data every ‘Nth’ column. For example, for list2, N=2, so we sum data every 2 column, in fact we need to sum data in C3, E3, G3 and I3. As N is not a fixed value, it can be any integer, so we need to take this into consideration when creating a formula.

 

FORMULA:

To figure out this problem, we can apply a formula based on SUMPRODUCT, MOD and COLUMN functions.

Step 1: In K2 enter the formula =SUMPRODUCT(–(MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2)=0),B2:I2).

Step 2: Click Enter to get return value. Verify that 140 is calculated correctly.

(In list1, N=1, sum data every 1 column means sum data in all columns, we can also use SUM(B2:I2) to sum total directly.)

Step 3: Copy the formula to K3 and K4. References are automatically updated in the formula.

We can see that total values are calculated correctly.

USAGE OF COLUMN and MOD FUNCTIONS

 We have introduced SUMPRODUCT function usage in previous articles. In this formula, the core part is the combination of MOD and COLUMN functions. We will have a brief review about SUMPRODUCT function when analyzing the workflow of each function in the formula.

 

The Usage of COLUMN Function

 For COLUMN function, the syntax as below:

=COLUMN ([reference])

COLUMN function can return the column number for a reference. The reference can be a cell or a range.

 

Usage 1: For example, when we enter ‘=column(B1)’ in D1, column number 2 is returned as B column is the second column in this worksheet. Thus, we can see that COLUMN function only returns the column number for the reference (B1 for example) and it is not affected by the reference value (15 in B1).

Usage 2: If the reference is a range, COLUMN function will return the column number of the leftmost column when pressing Enter directly after entering the formula. See example below. In this case B3:D6 contains 4 rows and 3 columns, COLUMN function will return the leftmost column (B column in this case), so 2 is returned.

Usage 3: But if the reference is a range, COLUMN function will return a horizontal array contains all column numbers in this range when selecting output part and pressing CTRL+SHIFT+ENTER. See example below. Enter =COLUMN(B3:D6) in E1, then in formula bar press F9, we can get an array {2,3,4}. So in this case, COLUMN function can return an array instead of an integer.

Usage 4: By the way, If the reference is omitted, COLUMN function returns current column number. See example below:

The Usage of MOD Function

For MOD function, the syntax as below:

=MOD(number, divisor)

MOD function can return the remainder of the two arguments ‘number is divided by divisor’. We can directly enter the two arguments number and divisor into formula to get remainder, see example below:

Remainder is 0. 0 is returned as 15 is divisible by 3, remainder is 0.

Remainder is not 0. 3 is returned because when dividing 15 by 4, it doesn’t return a whole number, the remainder is 3.

The two arguments also can be two references. Then MOD function will use the reference to execute formula. See example below:

In our instance =SUMPRODUCT(–(MOD(COLUMN(B3:I3)-COLUMN(B3)+1,J3)=0),B3:I3), ‘Number’ is a formula COLUMN(B3:I3)-COLUMN(B3)+1, ‘Divisor’ is a reference J3.

HOW THIS FORMULA WORKS:

After introducing above basic syntax, arguments, usage of COLUMN and MOD functions, we will analysis the working process of our formula in this article. Firstly, we will introduce each function workflow based on list1 and the formula in K2. In our instance list1, for snippet (MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2)=0), refer to COLUMN function usage#3, COLUMN(B2:I2) actually returns an array {2,3,4,5,6,7,8,9}, please see screenshot below. (select COLUMN(B2:I2) in formula bar, then press F9, you can see the array.)

As COLUMN(B2)=2, J2=1, so MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2) equals to MOD({2,3,4,5,6,7,8,9}-{2}+1,1). Please see screenshot below.

Continue to calculate the formula, we can get MOD({1,2,3,4,5,6,7,8},1).

In this formula, array {1,2,3,4,5,6,7,8} is argument ‘Number’, 1 is argument ‘Divisor’, as all numbers in the array are divisible by 1, so MOD({1,2,3,4,5,6,7,8},1)=0 equals to MOD({1,2,3,4,5,6,7,8})=0, compare each value in the array with 0, we can get below result.

Add double negative symbol (–) before TURE (or FALSE) result to convert TRUE to 1 and FALSE to 0, then the array converts to {1,1,1,1,1,1,1,1}, see screenshot below:

In list1, we can see that as value in J2 is 1, so sum data every 1 column is equivalent to sum all data in list B2:I2. But for list2, we need to sum data every second column, so actually we need to sum data in C3,E3,G3 and I3. Similar to list1, firstly we get all returned values of COLUMN function.

Now number argument is {1,2,3,4,5,6,7,8}, divisor is 2.

A number is divided by 2, the remainder is 0 or 1. After calculating MOD({1,2,3,4,5,6,7,8},2), we get an array {1,0,1,0,1,0,1,0}.

After comparing with 0, we can get TRUE or FLASE as result.

Convert TRUE to 1 and FALSE to 0 by adding double negative (–).

Now we come to the outermost function SUMPRODUCT. SUMPRODUCT function syntax has the following arguments:

=SUMPRODUCT(array1, [array2], [array3], …)

For list1, the last step is =SUMPRODUCT({1,1,1,1,1,1,1,1},B2:I2). We can see that there are two arrays, the first array is {1,1,1,1,1,1,1,1}, the second array is the values in B2:I2 {10,15,20,30,5,25,20,15}.

So, SUMPRODUCT({1,1,1,1,1,1,1,1},{10,15,20,30,5,25,20,15}) can be seen as value in one array multiplies by the corresponding value in another array, in this case the final array is equivalent to {1*10, 1*15,1*20,1*30,1*5,1*25,1*20,1*15}={10,15,20,30,5,25,20,15}, then sum data in the array {10,15,20,30,5,25,20,15}, 10+15+20+30+5+25+20+15=140.

For list2, the last step is =SUMPRODUCT({0,1,0,1,0,1,0,1},B3:I3). We can see that there are two arrays, the first array is {0,1,0,1,0,1,0,1}, the second array is the values in B3:I3 {20,10,30,40,25,15,35,45}.

So, for SUMPRODUCT({0,1,0,1,0,1,0,1},{20,10,30,40,25,15,35,45}), multiply value in array1 by the value in array2, so we get {0*20,1*10,0*30,1*40,0*25,1*15,0*35,1*45}={0,10,0,40,0,15,0,45}. SUMPRODUCT({0,10,0,40,0,15,0,45})=10+40+15+45=110.

RESULT:

Let’s check the formula workflow in F3. Actually, the workflow for list3 is the same, only divisor is changed to 3 for MOD part.

Step 1: Execute COLUMN function, convert all reference to real values.

Step 2: Execute MOD function.

Step 3: Compare values with 0 and convert TRUE/FALSE to 1/0.

Step 4: Execute SUMPRODUCT function, to sum data based on two arrays. SUMPRODUCT({0,0,20,0,0,35,0,0}=55. So we get 55 in K4.

SUMMARY:

1. COLUMN function returns an array if COLUMN function is entered as a horizontal array formula.
2. COLUMN function returns the leftmost column if COLUMN function is not entered as a horizontal array formula.
3. MOD function is often used in calculating ‘Nth’ rows/columns cases.
4. To sum ODD columns:

=SUMPRODUCT(–(MOD(COLUMN(list)-COLUMN(first cell)+1,2)=1),list)

5. To sum EVEN columns:

=SUMPRODUCT(–(MOD(COLUMN(list)-COLUMN(first cell)+1,2)=0),list)

Related Functions

---

• Excel SUMPRODUCT function
  The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel MOD function
  he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
• Excel COLUMN function
  The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….