Sum numbers between two specific cells

Dan1000

Board Regular
Joined
Nov 4, 2006
Messages
210
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
Cell Formulas
RangeFormula
F3=SUMIFS(B:B,A:A,F1,C:C,F2)
 
Upvote 0
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
 
Upvote 0
871263954

<tbody>
</tbody>

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:
Upvote 0
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!
 
Upvote 0
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

<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>
 
Upvote 0
What would happen if, for example, there were two 6s?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top