Summing alternate cells? 1

[gymbeef]

Total excel novice here, so apologize if a simple question, but...

I'm trying to write a spreadsheet that would display accumulated values based on a user-entered frequency. Is there a way to change the SUM function to skip values, or is there another function that would do what I need?

By way of example, if the frequency entered is "1" (i.e., every day), then it would add together every row (e.g. a1+a2+a3+...). If the frequency entered was "2" (every other day) then it would sum a1+a3+a5+a7..., if "3", then a1+a4+a7+a10...

Thanks in advance.

 

[Bowers74]

You will need to enter the following formula as an Array Formula (i.e. by pressing Ctrl+Shift+Enter instead of just Enter after you finish typing the formula).

=IF($A$2=0,0,SUM(IF(MOD(ROW(INDIRECT("1:"&COUNT($C$2:$C$20)))-1,$A$2)=0,$C$2:$C$20,"&quot))

This formula assumes that your frequency is in cell A2 and that the data you want to sum is in range C2:C20. You will need to modify it to fit your needs.

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[KenWright]

Assuming your data was in A1:A100 and that range was named MyData, and your frequency value was entered in E1, then the following formula would do it:-

=SUMPRODUCT(MyData*(MOD(ROW(MyData),$E$1)=0))

Without naming anything:-

=SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100),$E$1)=0))

Regards
Ken..................