Go Back

Sum every nth column

Read time: 20 minutes

You can use SUMPRODUCT, MOD and COLUMN functions to filter every nth column and sum the values in those columns.

Sum every nth column

Formula

= SUMPRODUCT((MOD(COLUMN(range)-COLUMN(range.first_column)+1,N)=0)*1,range)

Explanation

Range: Required. This range includes the values you want to sum.
Range.first_column: Required. This cell locates the first column of the range.
N: Required. This help to filter Nth column in the range.

In this formula, COLUMN functions get the relative column of the range. MOD function filters if the column number is divisible by N and returns an array of TRUE/FALSE elements. Finally, SUMPRODUCT will multiply and sum the equivalent values in the range that match TRUE returns in the array. 

Example 1

Below is some of the example of using this formula. The highlighted values in the range B4:G4 help you to spot the Nth columns.

 

Figure 1 – Sum every nth column

In I4, the formula is

=SUMPRODUCT((MOD(COLUMN(B4:G4)-COLUMN(B4)+1,H4)=0)*1,B4:G4)

First, COLUMN(B4:G4)-COLUMN(B4)+1,H4 portion is used to get relative column number is the range. It can be seen like this in arrays: {2,3,4,5,6,7} – 2 + 1 = {1,2,3,4,5,6}
Second, MOD(COLUMN(B4:G4)-COLUMN(B4)+1,H4)=0) returns and TRUE/FALSE array to filter the column number that divisible by H4 value. The array in this case is:{FALSE,TRUE, FLASE,TRUE, FLASE,TRUE} because {2,4,6} are divisible by H4=2. Multiplying this array by 1 (*1) will return an array like this: {0,1,0,1,0,1}.

Last, SUMPRODUCT uses the above array as the argument whose components you want to add and return the result.

Notes

You can adapt this formula to sum even or odd columns like this:

=SUMPRODUCT((MOD(COLUMN(B7:G7)-COLUMN(B7)+1,2)=1)*1,B7:G7)

Figure 2 – Sum every odd column

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

how can i add every nth cll in a column
Solved by F. F. in 16 mins
I need to add every nth cell in a column. i will be sharing the file on chat for further discussion
Solved by T. U. in 30 mins
I want to Copy a list (1.2.3.4.... eth) to every nth cell.
Solved by E. A. in 16 mins

Leave a Comment

avatar