Ask Your Question

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

asked 2018-11-14 03:00:46 +0100

timoseewho gravatar image

updated 2018-11-14 08:06:20 +0100

hello, i'm using Version: (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?



edit retag flag offensive 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.".

Lupp gravatar imageLupp ( 2018-11-14 08:54:08 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-11-14 03:45:13 +0100

robleyd gravatar image

updated 2018-11-14 05:40:29 +0100

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.

edit flag offensive delete link 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!

timoseewho gravatar imagetimoseewho ( 2018-11-14 04:07:40 +0100 )edit

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

timoseewho gravatar imagetimoseewho ( 2018-11-14 05:44:51 +0100 )edit

If the cells are text they'll be ignored

robleyd gravatar imagerobleyd ( 2018-11-14 05:56:21 +0100 )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?

timoseewho gravatar imagetimoseewho ( 2018-11-14 06:02:41 +0100 )edit

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

robleyd gravatar imagerobleyd ( 2018-11-14 06:20:09 +0100 )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>_<

timoseewho gravatar imagetimoseewho ( 2018-11-14 06:29:01 +0100 )edit

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.

robleyd gravatar imagerobleyd ( 2018-11-14 06:43:37 +0100 )edit

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

timoseewho gravatar imagetimoseewho ( 2018-11-14 06:56:06 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-14 07:25:06 +0100 )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:(

timoseewho gravatar imagetimoseewho ( 2018-11-14 08:07:06 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-14 03:00:46 +0100

Seen: 1,798 times

Last updated: Nov 14 '18