hello, i’m using Version: 6.1.1.2 (x64) here and was wondering how i would go about summing the contents of every nth row in the same column. for example, i wanna sum every 7th row in the column H starting from row 3 all the way up to row 493 (so row 3, 10, 17, etc.). what would i need to do?
thanks:)
I would suspect a sheet making such a formula (SUM) desirable to be organised generally in a every-seventh-row style. Something like a collection of something for 70 weeks… Or 70 articles with 7 properties each in rows instead of columns.
This is (next to) certainly bad design.
It may be a case of the sometimes so called X-Y phenomenon. In short: You might better have asked “What’s a good solution for X?” instead of “How to do Y?” without adding “I think to need it for X.”.
See if this article in the AOO Wiki helps.
Edit: make sure that the contents of the ranges you want to sum are numeric; use View | Value Highlighting. Text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted.
If this answer helped you, please accept it by clicking the check mark 
to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.
thanks for this! i was very hopeful but it doesn’t seem to work, can you give it a shot for me?
edit: alright! so it seems to work for some ranges but not others, trying to figure that out now, thanks!
ohh, so it won’t work consistently if the cells i’m trying to sum up are formulae (green)?
ps. i had no idea about Value Highlighting, this is cool!
so i’m just testing the formula with a purely numeric column but still getting a different result. can you help me diagnose what’s going wrong here?
130 (cell E486)
411
88
58
221
198
151
586
53
163
18
234 (cell E497)
=SUMPRODUCT(MOD(ROW(E486:E497), 4)=1, E486:E497), the result is 878 when i calculate 404 myself:(
If the cells are text they’ll be ignored
i’m sorry about the formatting, i’m pretty terrible at this lol
in the example above, all numeric cells, with that SUMPRODUCT formula, shouldn’t it be 130+221+53=404? why does the formula give me 878?
Please upload a sample .odt file showing your issue. I’ve given you karma so you should be able to do that.
alright so… i copied the same sample to a new sheet starting at row 1 as opposed to the 486 (edited the formula to reflect on the new rows) and it worked lol, gave me 404. does this formula only work when starting from row 1? i’ll get around to the sample thanks, but i’m thinking the answer is obvious here but i just don’t know>_<
It should work for arrays anywhere in a sheet; there is possibly something unexpected in the sample that fails. Without seeing it, it is not possible to diagnose it.
alright, i have it attached up top, you can scroll down to the E486 results:)
To see which rows are being considered, just copy the formula part =MOD(ROW(E486:E497); 4) to the cells to the right of the range, and see where they are 1. Then make corrections to the sum formula, like
=SUMPRODUCT(MOD(ROW(E486:E497)-486+1; 4)=1; E486:E497)
and if you need to start from Nth row in the range, then change the +1 to +N.
oh Mike i think you’re onto something here, i had no idea about that. i updated the attachment above, can you see what i’m doing wrong at row 499? i wanna sum every 7 rows starting from E479:(
Well - I see the updated spreadsheet (which you didn’t mention). And I see that you didn’t try my suggestions to account for the first row being not 1. Please read my suggested formula, and use the corrections accordingly.
i understand it now, i misunderstood lol
thanks a ton!
Seems to work as defined in your formula. Your formula says if mod(current row no/ 4) = 1 then sum it. So it counts every fourth row starting with 489.
As Mike suggests, break down the formula so you can see what values the individual parts of it are returning, and look at the Help to see what the various functions actually do.
If you want the first row summed to be E486, you would need to change the =1 to =2 as 486/4 has a remainder of 2
thanks a lot robleyd:)
both of you saved a lot of my time haha!