Sum numbers between two specific cells
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Sum numbers between two specific cells
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Nov 2006
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum numbers between two specific cells

    It works in the first row (row2) but when I drag down it does not work in any of the following rows

  2. #12
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,381
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sum numbers between two specific cells

    It’s going to be searching for 8 and 6 if you drag it down. Are you meaning to search for those same numbers in each row?
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #13
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,062
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sum numbers between two specific cells

    Quote Originally Posted by Dan1000 View Post
    The value would be 10 if the question was to sum the numbers between 8 and 6 in this instance. Always 9 cells next to each other. Always the numerals 1 to 9 in each of the cells.
    Hi, here is a formula based option you can try.

    Excel 2013/2016
    ABCDEFGHIJKLM
    1Num 1Num 2Result
    28712639548610

    Sheet1



    Worksheet Formulas
    CellFormula
    M2=SUM(INDEX(A2:I2,MATCH(K2,A2:I2,0)):INDEX(A2:I2,MATCH(L2,A2:I2,0)),-K2,-L2)

    Last edited by FormR; Aug 22nd, 2019 at 03:07 AM.
    [code]your code[/code]

  4. #14
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,449
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Sum numbers between two specific cells

    Edit: Oops, FormR posted basically same thing while I was composing my post.

    See if this is any use. It doesn't matter whether the 2 specified numbers are in the correct order (left to right) or not. It should sum the cells between anyway.

    Sum Between

    ABCDEFGHIJKLM
    1 Between 1Between 2Result
    2871263954 8610
    3671283954 8610
    4562147938 620
    5562147938 5832

    Spreadsheet Formulas
    CellFormula
    M2=SUM(INDEX(A2:I2,MATCH(K2,A2:I2,0)):INDEX(A2:I2,MATCH(L2,A2:I2,0)))-SUM(K2:L2)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Aug 22nd, 2019 at 03:09 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #15
    Board Regular
    Join Date
    Nov 2006
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum numbers between two specific cells

    Quote Originally Posted by FormR View Post
    Hi, here is a formula based option you can try.

    Excel 2013/2016
    A B C D E F G H I J K L M
    1 Num 1 Num 2 Result
    2 8 7 1 2 6 3 9 5 4 8 6 10
    Sheet1

    Worksheet Formulas
    Cell Formula
    M2 =SUM(INDEX(A2:I2,MATCH(K2,A2:I2,0)):INDEX(A2:I2,MATCH(L2,A2:I2,0)),-K2,-L2)
    Now you're a grand master...don't think, feeeeeeel!...thank you soooooo much

    Dan
    Last edited by Peter_SSs; Aug 22nd, 2019 at 09:01 PM. Reason: Added quote tag

  6. #16
    Board Regular
    Join Date
    Nov 2006
    Posts
    197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum numbers between two specific cells

    Thanks Peter. Always find your help with Excel top notch! No worries getting there after the grand master

  7. #17
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,449
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Sum numbers between two specific cells

    Quote Originally Posted by Dan1000 View Post
    Thanks Peter. Always find your help with Excel top notch! No worries getting there after the grand master
    No problem. Glad you got a successful outcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

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
  •