# 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: 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)

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

Related Posts

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Count Cells that are Case Sensitive

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Check Dates in chronological order

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel's Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Average per Week by Formula in Excel

We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation ...

How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How To Sum the Largest N Values in Excel

Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...

How to Sum for Cell Contains Formula Only in Excel

Sometimes values are created by formulas in cells. If we want to sum values which are created by formulas from a range, but some values which are hardcoded also list in the same range, how can we filter out matched ...

How to Sum the Smallest N Values in Excel

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

Sidebar