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.

 

[Dan1000]

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

 

[lrobbo314]

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?

 

[FormR]

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

Cell Formulas
Range	Formula
M2	=SUM(INDEX(A2:I2,MATCH(K2,A2:I2,0)):INDEX(A2:I2,MATCH(L2,A2:I2,0)),-K2,-L2)

 

[Peter_SSs]

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.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1											Between 1	Between 2	Result
2	8	7	1	2	6	3	9	5	4		8	6	10
3	6	7	1	2	8	3	9	5	4		8	6	10
4	5	6	2	1	4	7	9	3	8		6	2	0
5	5	6	2	1	4	7	9	3	8		5	8	32
Sum Between

 

[Dan1000]

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
Num 1	Num 2	Result

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]10[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=SUM(INDEX(A2:I2,MATCH(K2,A2:I2,0)):INDEX(A2:I2,MATCH(L2,A2:I2,0)),-K2,-L2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Now you're a grand master...don't think, feeeeeeel!...thank you soooooo much

Dan

 

[Dan1000]

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

 

[Peter_SSs]

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.