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
1											Num 1	Num 2	Result
2	8	7	1	2	6	3	9	5	4		8	6	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula M2 =SUM(INDEX(A2:I2,MATCH(K2,A2:I2,0)):INDEX(A2:I2,MATCH(L2,A2:I2,0)),-K2,-L2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.