How to Sum Every Nth Column in Excel

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:

Sum Every Nth Column 1

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

Sum Every Nth Column 2

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

Sum Every Nth Column 3

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

Sum Every Nth Column 4

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

Sum Every Nth Column 5

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.

Sum Every Nth Column 6

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.

Sum Every Nth Column 7

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

Sum Every Nth Column 8

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.

Sum Every Nth Column 9

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

Sum Every Nth Column 10

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.

Sum Every Nth Column 11

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

Sum Every Nth Column 12

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.

Sum Every Nth Column 13

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

Sum Every Nth Column 14

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.

Sum Every Nth Column 15

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:

Sum Every Nth Column 16

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.

Sum Every Nth Column 17

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

Sum Every Nth Column 18

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

Sum Every Nth Column 19

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

Sum Every Nth Column 20

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

Sum Every Nth Column 21

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

Sum Every Nth Column 22

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

Sum Every Nth Column 23

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.

Sum Every Nth Column 24

Step 2: Execute MOD function.

Sum Every Nth Column 25

Step 3: Compare values with 0 and convert TRUE/FALSE to 1/0.
Sum Every Nth Column 26

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.

Sum Every Nth Column 27

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)

  1. 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])….

 

 

Sidebar