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
Leave a Comment