SUM Formula to Add Every Nth Row

[adamginger]

Hello!

I am having some trouble trying to add cell values together starting from N14 through to N1805.
I would like the formula to include these cells and every 9th cell in between.
For Example, the first cell in the sum would be N14, then N23... Through to N1805.

I believe it is a MOD function that I would need to use however I cannot get other examples work for my application.

If you could also break down your reply as to how this formula works, it would be appreciated.

Thanks in advanced!

 

[adamginger]

Apologies, the cells are N13 through N1804 (Inclusive)

 

[BobUmlas]

=SUMPRODUCT(N(MOD(ROW(N14:N1805),9)=5),N14:N1805)

ROW(N14:N1805) is 14;15;16;...;1805
MOD(that,9) is 5,6,7,8,0,1,2,3,4,5,6,7,8,0...
When that's a 5, we have one of the cells wanted, so
N(MOD(ROW(...))) is true;false;false;false... and whenever the remainder is 5, it's true.
Multiply these by N14 thru N1805 will be N14 * true (or N14), N15 * false (or 0),etc. Only the values from N14, N23, N32, etc will be added together.

 

[adamginger]

Fantastic, This is perfect.

Thank You!

 

[T. Valko]

Apologies, the cells are N13 through N1804 (Inclusive)

Try one of these...

If you will never insert new rows before the data range:

Array entered**...

=SUM(IF(MOD(ROW(N13:N1804),9)=4,N13:N1804))

If you might insert new rows before the data range:

Array entered**...

=SUM(IF(MOD(ROW(N13:N1804)-ROW(N13),9)=0,N13:N1804))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[adamginger]

Okay, so I have managed to get this to work for cells N13 though N1804, which is great however if I change the formula to do the same with N11 through N1802 I cannot get the cells to add together, even though they are still 9 cells apart from each other. I don't know if it's obvious but I can seem to get it to work.
Any help would be appreciated
Thanks for your previous responses.

 

[adamginger]

Okay, so I have managed to get this to work for cells N13 though N1804, which is great however if I change the formula to do the same with N11 through N1802 I cannot get the cells to add together, even though they are still 9 cells apart from each other. I don't know if it's obvious but I can seem to get it to work.
Any help would be appreciated
Thanks for your previous responses.

Nevermind I have managed to work out a solution!]

 

[T. Valko]

Good deal. Thanks for the feedback!