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

1. ## 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. ## 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)

3. ## 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. ## 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.

5. ## Re: Sum numbers between two specific cells

What did you add to get 10?

6. ## 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. ## 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)

8. ## Re: Sum numbers between two specific cells

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

9. ## 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?!

10. ## 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.