Count Rows until two values are matched

[sjfolly23]

Hi, I have tried several ways to achieve the result I want but can't quite get there|

I have two columns, Column A = Y, Column B = Team.

A B
Team 1
Team 3
Y Team 2
Y Team 1

I Want to count the number of rows until I get a Y in column A and Team 1 In Colum B, eg 4

Hope someone can help.
Regards

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try something like this

Pasta2
	A	B	C	D	E
1	Header1	Header2		Team	Result
2		Team 1		Team 1	4
3		Team 1		Team 2	3
4	Y	Team 2		Team 3	6
5	Y	Team 1
6		Team 3
7	Y	Team 3
Plan9

Cell Formulas
Range	Formula
E2:E4	E2	=AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/((A$2:A$100="Y")*(B$2:B$100=D2)),1)

Hope this helps

M.

 

[sjfolly23]

Hi Marcelo,

Thank you for quick response, works brilliantly.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.