Sum every nth column

★ 20 minutes read

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar