How to count until a certain value?

[CHeiring]

Hi

I have a spreadsheet in which I have a Column in which a "1" indicated the beginning of a section of time and "2" indicates the end of this section.

I would like to be able to from top to bottom to go through column A (see Image), and the count the number of rows between the first time a cell contain "1" until a cell contains "2" - number of rows will equal duration. It should then continue for every "1" and "2". The result = number of rows between each "1" and "2" pair should show in column B. In Column C I have shown how I would like the results to be presented.

Is this possible to do in Excel with formulas?

Many thanks in advance

Cheers

Christian

 

[AlanY]

try this

Book1
	A	B
1
2
3
4
5	1
6
7
8	2	3
9
10	1
11
12
13
14
15
16	2	6
Sheet1

Cell Formulas
Range	Formula
B5:B16	B5	=IF(A5=2,LOOKUP(2,1/($A$1:A5=2),ROW($A$1:A5))-LOOKUP(2,1/($A$1:A5=1),ROW($A$1:A5)),"")

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B
1
2
3
4
5	1
6
7
8	2	3
9
10	1
11
12
13
14
15
16	2	6
17
18
Main

Cell Formulas
Range	Formula
B2:B18	B2	=IF(A2=2,ROW()-XMATCH(1,A$1:A1,0,-1),"")

 

[CHeiring]

Hi Alan
Many thanks for taking time to help and this looks like it could work.

However, I can't get the formula to work if I copy it into my version of Excel (danish version) . In my version of Excel I can choose between 3 different "Lookup" - in danish "OPSLAG" - VOPSLAG, LOPSLAG or XOPSLAG. Also I am confused about the "/" in the LOOKUP argument.

 

[CHeiring]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B
1
2
3
4
5	1
6
7
8	2	3
9
10	1
11
12
13
14
15
16	2	6
17
18
Main

Cell Formulas
Range	Formula
B2:B18	B2	=IF(A2=2,ROW()-XMATCH(1,A$1:A1,0,-1),"")

Perfect - that seems to work

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[CHeiring]

Glad we could help & thanks for the feedback.

Can I be allowed an additional question. The method suggested with Xmatch works well and gives me a number when relevant. This number I need to use for calculation, fx median duration of an episode. However Excel consider the value as a textstring even though the cells are formatted as numbers. If fx I want to sum the values or multiply I get an error-message

 

[Fluff]

Both the formulae posted here will return numbers & not text.

 

[CHeiring]

Can I be allowed an additional question. The method suggested with Xmatch works well and gives me a number when relevant. This number I need to use for calculation, fx median duration of an episode. However Excel consider the value as a textstring even though the cells are formatted as numbers. If fx I want to sum the values or multiply I get an error-message

nevermind - figured it out my self. Turns out I had an If-statement combining numbers and text somewhere in the data flow towards the column I needed help with.

 

[Fluff]

Glad you sorted it & thanks for letting us know.