Page 1 of 2 12 LastLast
Results 1 to 10 of 14

cumulative sum of even or odd rows

This is a discussion on cumulative sum of even or odd rows within the Excel Questions forums, part of the Question Forums category; I need a formula to cumulatively sum the even rows or columns. Example: A B 10 10 20 20 60 ...

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    30

    Default cumulative sum of even or odd rows

    I need a formula to cumulatively sum the even rows or columns.

    Example:

    A B
    10 10
    20 20
    60 70
    75 95

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: cumulative sum of even or odd rows

    Try this in B1 and filled down.

    =SUMPRODUCT(--(MOD(ROW(A$1:A1),2)=MOD(ROW(A1),2)),A$1:A1)
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: cumulative sum of even or odd rows

    Here's a more efficient and simpler method..

    Excel 2010
    AB
    11010
    22020
    36070
    47595
    52090
    6100195

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=A1
    B2=A2
    B3=B1+A3
    B4=B2+A4
    B5=B3+A5
    B6=B4+A6

    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member
    Join Date
    Nov 2015
    Posts
    30

    Default Re: cumulative sum of even or odd rows

    Its working fine but i want it to make it on columns from F35:ZZ35

  5. #5
    New Member
    Join Date
    Nov 2015
    Posts
    30

    Default Re: cumulative sum of even or odd rows

    it's working fine, but i need it on columns from F35:ZZ35

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: cumulative sum of even or odd rows

    Same concept, just change from row to column..

    Here's both methods

    Excel 2010
    FGHIJK
    351020607520100
    36
    371020709590195
    381020709590195

    Sheet1



    Worksheet Formulas
    CellFormula
    F37=SUMPRODUCT(--(MOD(COLUMN($F35:F35),2)=MOD(COLUMN(F35),2)),$F35:F35)
    G37=SUMPRODUCT(--(MOD(COLUMN($F35:G35),2)=MOD(COLUMN(G35),2)),$F35:G35)
    H37=SUMPRODUCT(--(MOD(COLUMN($F35:H35),2)=MOD(COLUMN(H35),2)),$F35:H35)
    I37=SUMPRODUCT(--(MOD(COLUMN($F35:I35),2)=MOD(COLUMN(I35),2)),$F35:I35)
    J37=SUMPRODUCT(--(MOD(COLUMN($F35:J35),2)=MOD(COLUMN(J35),2)),$F35:J35)
    K37=SUMPRODUCT(--(MOD(COLUMN($F35:K35),2)=MOD(COLUMN(K35),2)),$F35:K35)
    F38=F35
    G38=G35
    H38=F38+H35
    I38=G38+I35
    J38=H38+J35
    K38=I38+K35

    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    New Member
    Join Date
    Nov 2015
    Posts
    30

    Default Re: cumulative sum of even or odd rows

    thats great but it's only working on even columns what about odd ones ?

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: cumulative sum of even or odd rows

    I may be missing something, but it looks like it works to me as shown in post #6
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    New Member
    Join Date
    Nov 2015
    Posts
    30

    Default Re: cumulative sum of even or odd rows

    Unfortunately the formula is not working, it says #VALUE!

  10. #10
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    42,171

    Default Re: cumulative sum of even or odd rows

    Which one?

    I'd suggest the simpler method (not the sumproduct)

    Perhaps your numbers are not really numbers..
    What does this return
    =ISNUMBER(F35)
    And fill it accross.
    Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Page 1 of 2 12 LastLast

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
  •  


DMCA.com