Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles SUM Sum every nth column

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc