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

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

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

    Default Sum numbers between two specific cells

    I need a formula to sum the values in a series of cells where the cell to the left is a specific number and the cell to the right is a different specific number.

    Any help would be much appreciated.

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

    Default Re: Sum numbers between two specific cells

    Hi, something like this?

    Excel 2013/2016
    ABCDEF
    1specific number 1sum rangespecific number 2Specific #110
    259243Specific #220
    3103420Sum189
    4106565
    557554
    6107920
    776311
    8202332
    959742
    10107620
    1158142

    Sheet1



    Worksheet Formulas
    CellFormula
    F3=SUMIFS(B:B,A:A,F1,C:C,F2)

    [code]your code[/code]

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

    Default Re: Sum numbers between two specific cells

    Hi FormR

    Thats a step further than I anticipated and clearly shows you have great Excel skill! Can you show me a formula that will work on a single row and one that will work on a single column, please?

    Thank you

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

    Default Re: Sum numbers between two specific cells

    8 7 1 2 6 3 9 5 4

    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.
    Last edited by Dan1000; Aug 22nd, 2019 at 01:27 AM. Reason: Mistake

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum numbers between two specific cells

    What did you add to get 10?

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

    Default Re: Sum numbers between two specific cells

    Like it says - the numbers between 8 and 6, that is the 7, 1 and 2, in this instance, although the numbers may be in any order so long as the formula works out the sum between two of the specific numbers 1 to 9 .

    I hope thats clear!

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

    Default Re: Sum numbers between two specific cells

    You can use this UDF.

    Code:
    Function SUMBETWEEN(r As Range, StartNum As Long, EndNum As Long) As Long
    Dim AR() As Variant: AR = r.Value
    Dim b As Boolean: b = False
    Dim Total As Long: Total = 0
    
    
    For i = LBound(AR) To UBound(AR, 2) - 1
        If b Then Total = Total + AR(1, i)
        If AR(1, i) = StartNum Then b = True
        If AR(1, i + 1) = EndNum Then b = False
    Next i
    
    
    SUMBETWEEN = Total
    End Function
    LROBBO HTML
    ABCDEFGHIJ
    1Formula
    287126395410


    Formulas - Array formulas require Ctrl+Shift+Enter
    J2=SUMBETWEEN(A2:I2,8,6)
    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.

  8. #8
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum numbers between two specific cells

    What would happen if, for example, there were two 6s?

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

    Default Re: Sum numbers between two specific cells

    Always the nmbers 1 through to 9.
    Always only 9 numbers.
    Never two or more of the same numbers.

    =sumbetween works in the first row but not in the next rows - not sure what it is doing?!
    Last edited by Dan1000; Aug 22nd, 2019 at 02:36 AM.

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

    Default Re: Sum numbers between two specific cells

    You need to Hit Alt+F11 to get into the VB editor. Then Hit Alt+I+M to insert a new module. Then paste the code that I posted previously. Then the formula should work.
    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.

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
  •