Summing every other n columns

Posted by Fendi on February 11, 2002 9:37 AM

Hi,
I am trying to write a function that would sum every other n columns. The function would ideally look like this:
SumEvery(A2:L2,1,2)
A2:L2 = row to sum
1 = start adding from the first column
2 = add every other 2 columns.

This function would ideally result in A2+D2+G2+J2

thanks

Posted by Mark W. on February 11, 2002 10:23 AM

BTW, here are array formulas that do it...

{=SUM(IF({1,0,0,1,0,0,1,0,0,1,0,0},$A2:$L2))}

or, if you like...

{=SUM(IF(MID(REPT(100,4),COLUMN($A:$L),1)+0,$A2:$L2))}

Note: These are array formulas which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Mark W. on February 11, 2002 4:52 PM

Just for fun...

...here are some variants using the 2nd formula...

Sum every 3rd column beginning with the 2nd...
{=SUM(IF(MID(REPT("010",4),COLUMN($A:$L),1)+0,$A2:$L2))}

Sum every 4th column begining with the 4th...
{=SUM(IF(MID(REPT("0001",3),COLUMN($A:$L),1)+0,$A2:$L2))}

Sum every other column beginning with the 2nd...
{=SUM(IF(MID(REPT("01",6),COLUMN($A:$L),1)+0,$A2:$L2))}

This is just too much fun!! : )