# [Calc] How to sum every nth row in the same column?

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:)

C:\fakepath\sample.ods

edit retag close merge delete

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.".

( 2018-11-14 08:54:08 +0200 )edit

Sort by » oldest newest most voted

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.

more

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!

( 2018-11-14 04:07:40 +0200 )edit

ohh, so it won't work consistently if the cells i'm trying to sum up are formulae (green)?

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:(

( 2018-11-14 05:44:51 +0200 )edit
1

If the cells are text they'll be ignored

( 2018-11-14 05:56:21 +0200 )edit

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?

( 2018-11-14 06:02:41 +0200 )edit
1

Please upload a sample .odt file showing your issue. I've given you karma so you should be able to do that.

( 2018-11-14 06:20:09 +0200 )edit

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>_<

( 2018-11-14 06:29:01 +0200 )edit
1

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.

( 2018-11-14 06:43:37 +0200 )edit

alright, i have it attached up top, you can scroll down to the E486 results:)

( 2018-11-14 06:56:06 +0200 )edit
1

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.

( 2018-11-14 07:25:06 +0200 )edit

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:(

( 2018-11-14 08:07:06 +0200 )edit