Sum numbers between two specific cells

[Dan1000]

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.

 

[FormR]

Hi, something like this?

Excel 2013/2016
	A	B	C	D	E	F
1	specific number 1	sum range	specific number 2		Specific #1	10
2	5	92	43		Specific #2	20
3	10	34	20		Sum	189
4	10	65	65
5	5	75	54
6	10	79	20
7	7	63	11
8	20	23	32
9	5	97	42
10	10	76	20
11	5	81	42
Sheet1

Cell Formulas
Range	Formula
F3	=SUMIFS(B:B,A:A,F1,C:C,F2)

 

[Dan1000]

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

 

[Dan1000]

[TABLE="width: 474"]
<tbody>[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[kweaver]

What did you add to get 10?

 

[Dan1000]

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!

 

[lrobbo314]

You can use this UDF.

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

<style type="text/css">table.LEGO-table {font-size: 12px;border: 1px solid #CCC;font-family: Arial, Helvetica, sans-serif;border-collapse: collapse;}.LEGO-table td {padding: 4px;margin: 3px;border: 0.1px solid #000000;}.LEGO-table th {background: linear-gradient(0deg, rgba(9,121,22,1) 24%, rgba(51,51,51,1) 100%);color: #FFF;font-weight: bold;border-collapse: separate;border: 0.1px solid #000000;}td.blank {background-color: #ebebeb;text-align: center;}.LEGO-table tr:nth-child(even) {background-color: #d9d9d9;}</style><table class="LEGO-table"><caption>LROBBO HTML</caption><thead><tr class="LEGO-firstrow"><tr><td class="blank"></td><td class="blank">A</td><td class="blank">B</td><td class="blank">C</td><td class="blank">D</td><td class="blank">E</td><td class="blank">F</td><td class="blank">G</td><td class="blank">H</td><td class="blank">I</td><td class="blank">J</td></tr><tr><td class="blank">1</td><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th></th><th>Formula</th></thead><tbody><tr><td class="blank">2</td><td>8</td><td>7</td><td>1</td><td>2</td><td>6</td><td>3</td><td>9</td><td>5</td><td>4</td><td>10</td></tr></tbody></table></br></br><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00; color:#000000; "><tbody><tr><td><b>Formulas - Array formulas require Ctrl+Shift+Enter</b></td></tr><tr><td><table class="LEGO-table"><tr><td>J2</td><td>=SUMBETWEEN(A2:I2,8,6)</td></tr></table></tbody></table>

 

[kweaver]

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

 

[Dan1000]

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?!

 

[lrobbo314]

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.