Summing every 7th column

[mr_hiboy]

Hi,


I'm trying to put a formula in cell D66, to sum K66 and every 7th column after (i.e. Q66, W66 etc), need to sum up to 30 cells.


I've been playing with INDEX but not quite getting there with it.


Cheers

Paul

 

[Faseeh]

Hi mr_hiboy,


Assuming your data is present in cells A1:K30, enter this formula in A31 and drag to left:

=IF(MOD((COLUMN()+7)/8,1)=0,SUM(A1:A30),"")

Regards,

Faseeh

 

[NARAYANK991]

Hi Paul ,


Try this :


=SUMPRODUCT(--(MOD(COLUMN(K66:QQ66),6)=5),K66:QQ66)


where QQ66 is just an extreme address ; if you can fix an end address , use it instead.


Courtesy : http://www.mrexcel.com/forum/excel-questions/316078-sum-alternate-cells.html


Narayan

 

[mr_hiboy]

Thanks for the speedy responses guys.


I've gone with option 2, works a treat.


thanks again, Paul

 

[mr_hiboy]

Actually can you help with this,


I've copied the formula from D66 to F66, in the hope that it would then do the same for data in M66 onwards, but it's not calculating the correct number.


=SUMPRODUCT(--(MOD(COLUMN(K66:QQ66),6)=5),K66:QQ66)


=SUMPRODUCT(--(MOD(COLUMN(M66:QS66),6)=5),M66:QS66)


Also, if I wanted to change it to every 6 column what do I need to change. I don't quite get what the ,6=5 bit is doing.


cheers

 

[mr_hiboy]

Figured it out, changed the 5 to 1

 

[DonMinter]

Just a quick question: Would this have been easier with R1C1 notation? I don't use it, but maybe someone who understands it can reply.