Excel - sum every nth cell in a row

Asked By BNT1 via OfficeKB.com on 21-Mar-08 03:59 PM
Hi

I am trying to adapt Bob Phillips formula

=SUMPRODUCT((B2:B32)*(MOD(ROW(B2:B32)-ROW(B2),n)+1=m))

I have cells, e5,e6,e7,e8,e9,e10,e11,e12,e13,e14,e15, etc
I want to sum, e5,e9,e13 etc
I will need to adapt this in another cell to add, e6,e10,e14 etc and
therefore need to understand the N + M value

I have entered the formula below in cell c5, but get 0.  Have entered as
array formula
{=SUMPRODUCT((E5:AY5)*(MOD(ROW(E5:AY5)-ROW(B2),4)+1=F5))}

What am I doing wrong

Help appriciated

Brian

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200803/1




ETLahr replied on 21-Mar-08 04:23 PM
I have used this formula for summing up the 3rd row.

=SUM(IF(MOD(ROW($O$3:$O$425),$O$435)=0,$O$3:$O$425,0))

Cell O435's value is 3 in this case.

So it takes the sum of the cell if the remainder or the row# divided by 3
(cell O435) of the is equal to 0.  You should be able to use this and assign
an unused cell to 4, and use that as your divisor. and you can change the =0
to be anything between 0 and 1 number less than you are using as you divisor.

Hope this helps.