Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: sum values on even row numbers

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Morning all,

    I thought this would be a 2-minute formula but it's eluded me this morning

    Range A1 to A20 contains values

    I just want a single formula that will sum those sitting on even row numbers

    many thanks
    Chris

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not flash, but works.
    =A2+A4+A6+A8+A10+A12+A14+A16+A18+A20
    Derek

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'Day Derek,

    yeah, my ranges are actually a lot bigger, so ideally I'm trying to use something based on that row number or an even/odd basis

    sorry, I should have been clearer mate !

    Chris

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See Tip Of the Day for Thursday, January 24, 2002

    http://www.mrexcel.com/weblog/weblog.shtml
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Guest

    Default

    Chris,

    I believe that this should do the trick for you:

    =SUM(IF(MOD(ROW(A1:A20),2)=0,A1:A20))

    Enter it as an array formula by pressing Ctrl+Shift+Enter all at once.


    enjoy

    Bariloche

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'Day Chris
    Sorry mate, I just couldn't resist it.
    This is probably something you can do with an array formula but I can only do it the hard way by putting this in B1, scrolling down, then summing it
    =IF(ISEVEN(ROW()),A1,0)
    Perhaps this will help until someone cleverer than me comes along
    Have a good day
    Derek

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Fab, thanks guys

    Juan Pablo - thanks, I should remember about those tips : one quick question though, I couldn't understand what the "-row(A1)" in the mod function was there for

    =SUM((MOD(ROW(A1:A250)-ROW(A1),2)=0)*(A1:A250))

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For that, i give FULL CREDIT to Aladin...

    The -ROW(A1) is to "adapt" the formula to whatever range, doesn't matter in which row it starts. In other words, "offset" the range to row 0 (Or was it 1 ?)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

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
  •