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:)
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:(
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?
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>_<
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:(
Asked: 2018-11-14 03:00:46 +0100
Seen: 1,931 times
Last updated: Nov 14 '18
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
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.".