How many times does an "x" occur before a "y"?

[danzilla911]

Hi everyone,

I run a monthly bowling tournament and I want to find a way to calculate how many events each player has participated in since the last time they cashed. I have the devised following system to keep track of the results.

Key:
2 = Cashed
1 = Participated but did not cash
Blank = Did not participate

Player	Event 4	Event 3	Event 2	Event 1		Answer
A			1			1
B		1	1	1		3
C	2		1			0
D	1	2	2	2		1
E	1	1	2	2		2

The answers I calculated by hand are included in the far right column.

The basic calculation I am trying to figure out is how many "1's" appear before a "2" for each person. Blank cells do not count.

I am somewhat experienced in Excel but I cannot figure out a formula or even find another example of how to make this work. Any ideas would be greatly appreciated.

Thank you!

- Dan

 

[Eric W]

Welcome to the MrExcel forum!

I'm sure there's a better way, but try:

Book1
	A	B	C	D	E	F	G
1	Player	Event 4	Event 3	Event 2	Event 1		Answer
2	A			1			1
3	B		1	1	1		3
4	C	2		1			0
5	D	1	2	2	2		1
6	E	1	1	2	2		2
Sheet2

Cell Formulas
Range	Formula
G2:G6	G2	=IFERROR(COUNTIF(A2:INDEX(B2:E2,MATCH(2,B2:E2,0)),1),SUM(B2:E2))

 

[danzilla911]

Welcome to the MrExcel forum!

I'm sure there's a better way, but try:

Book1
	A	B	C	D	E	F	G
1	Player	Event 4	Event 3	Event 2	Event 1		Answer
2	A			1			1
3	B		1	1	1		3
4	C	2		1			0
5	D	1	2	2	2		1
6	E	1	1	2	2		2
Sheet2

Cell Formulas
Range	Formula
G2:G6	G2	=IFERROR(COUNTIF(A2:INDEX(B2:E2,MATCH(2,B2:E2,0)),1),SUM(B2:E2))

I think you nailed it! Thanks alot. You saved the day!

 

[Peter_SSs]

Welcome to the MrExcel board!

What about this way?

23 05 08.xlsm
	A	B	C	D	E	F	G
1	Player	Event 4	Event 3	Event 2	Event 1		Answer
2	A			1			1
3	B		1	1	1		3
4	C	2		1			0
5	D	1	2	2	2		1
6	E	1	1	2	2		2
Count since cashed

Cell Formulas
Range	Formula
G2:G6	G2	=FIND(2,CONCAT(B2:E2,2))-1