Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Sum rows every nth column

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    235
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum rows every nth column

    Is there a way to sum a range of rows every nth column. For example I want to sum rows 10 - 30 every 4th column. Can I use SUMIFS somehow to do this?

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,311
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum rows every nth column

    Do you mean like this:

    Assuming rng: B10:N13
    B15: =SUMPRODUCT(rng*(MOD(COLUMN(rng)-COLUMN(INDEX(rng,,1)),4)=0))
    Or =SUMPRODUCT(B10:N13*(MOD(COLUMN(B10:N13)-COLUMN(B10),4)=0))

    Excel 2010
    BCDEFGHIJKLMN
    1015913
    11261014
    12371115
    13481216
    14
    15136

    1



    Last edited by StephenCrump; Dec 6th, 2017 at 11:26 PM.

  3. #3
    Board Regular
    Join Date
    Oct 2017
    Location
    Tasmania
    Posts
    132
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum rows every nth column

    Something like this? You'll need to hit CONTROL+SHIFT+ENTER as the formula uses arrays.

    A B C D ... AB
    1 n= 4
    2 Sum columns: =SUM(IF(MOD(COLUMN(A$5:AB$104),B1)=0,A$5:AB$104))
    3
    4
    5 0.371155968 0.702672 0.468813 0.410523 ... 0.396036
    6 0.426536578 0.464798 0.02317 0.242622 ... 0.174385
    7 0.424560384 0.243813 0.21639 0.327721 ... 0.001815
    8 0.043125384 0.579772 0.04318 0.087144 ... 0.319651
    9 0.711210033 0.584506 0.583156 0.307808 ... 0.160351
    ... ... ... ... ... ... ...
    102 0.217490312 0.205901 0.017295 0.554897 ... 0.310382
    103 0.34791251 0.840192 0.504437 0.080458 ... 0.917384
    104 0.122595243 0.650957 0.587608 0.886758 ... 0.405192
    Last edited by NiMip; Dec 6th, 2017 at 11:29 PM. Reason: Improved formula with MOD

  4. #4
    Board Regular
    Join Date
    Oct 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum rows every nth column

    Try this
    =SUMPRODUCT(N(MOD(ROW(A10:A30),4)=1),A10:A30)

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •