Count formula from right to confirm the word "Win" together

[ronie85]

Hi All

I require a formula that counts the word "win" if it happens in an occurance from the righthand side.
In the below example the word "Win" appears 3 times however I need the result to be 2 as the formula should dictace that the "Win" in cell J2 is the last in the sequence, and counting backwards from the right that it appears consequative in cells J2 and I2 therefore the result is 2.
If cell K2 is "Win" then the result should change to 3. However if cell K2 says "Lost" then the result will then go back to 0.

I hope this makes sense.

Thanks in advance.

 

[Momentman]

Something like this maybe

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Team	Season		League	1	2	3	4	5	6	7	8	9	10	11	12	13	Result
2	Aberdeen	22/23		51	Draw	Win	Lost	Draw	Win	Win								2
3					Win	Win	lost	Win	Win	Win	Win							4
4					Win	lost	lost	Win	Win	lost	lost							0
5					Win	Win	Win	Win	Win	Win	Win	Win	Win					9
Sheet1

Cell Formulas
Range	Formula
R2:R5	R2	=LET(a,FILTER(E2:Q2,E2:Q2<>""), IF(TAKE(a,,-1)<>"Win",0, COUNTA(a)-XLOOKUP(TRUE,a<>"Win",SEQUENCE(,COUNTA(a)),0,,-1)))

It may be elegant in the morning when I am fully awake

 

[DRSteele]

I think you're trying to ascertain the current streak. If so, this oughtta do er.

MrExcel posts20.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2
3	Match	1	2	3	4	5	6	7	8	9	10	11		Currrent Streak
4	Outcome	LOSS	LOSS	DRAW	WIN	LOSS	LOSS	DRAW	LOSS	DRAW	WIN	WIN		WIN 2
5
Sheet27

Cell Formulas
Range	Formula
P4	P4	=INDEX($D4:$N4,COLUMNS($D4:$N4))&" "& IF(COUNTIF($D4:$N4,$D4)=COUNTA($D4:$N4),COUNTA($D4:$N4),MAX(($D4:$N4<>"")*COLUMN($D4:$N4))-MAX((D4:N4<>INDEX($D4:$N4,COLUMNS($D4:$N4)))*($D4:$N4<>"")*COLUMN($D4:$N4)))

 

[Rick Rothstein]

Here is another formula that will also work...

=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(0&CONCAT(IF(r="","",0+(r="Win"))),"0",-1))))

 

[Momentman]

Here is another formula that will also work...

=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(0&CONCAT(IF(r="","",0+(r="Win"))),"0",-1))))

Very Elegant as always @Rick Rothstein

 

[ronie85]

Very Elegant as always @Rick Rothstein

This works great thanks

 

[Peter_SSs]

Here is another formula that will also work...

=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(0&CONCAT(IF(r="","",0+(r="Win"))),"0",-1))))

That's not like you Rick - there are two double quote marks not required ("0")

This is the one (fairly similar but slightly shorter) I came up with

=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(CONCAT(LEFT(r,1)),{"D","L"},-1,,1))))

 

[Rick Rothstein]

That's not like you Rick - there are two double quote marks not required ("0")

This is the one (fairly similar but slightly shorter) I came up with

=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(CONCAT(LEFT(r,1)),{"D","L"},-1,,1))))

One of the earlier iterations of my formula required those quotes... as I modified and rearranged things, I never went back to see if they were still required. My bad. As for your formula, I would note that it is case sensitive and, as such, could be something the OP needs to be aware of. The only reason I noticed this is because I used @Momentman's sample data when testing and some of his entries are in all lower case... for those, your formula failed which is what alerted me.

 

[Fluff]

Peter's formula can be made case-insensitive with one slight tweak

=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(CONCAT(LEFT(r,1)),{"D","L"},-1,1,1))))

 

[Peter_SSs]

Peter's formula can be made case-insensitive with one slight tweak

=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(CONCAT(LEFT(r,1)),{"D","L"},-1,1,1))))

Yes, I had used that extra "1" in all my testing as I too had been using the sample data from post 2. However, as I went to post my suggestion I noted that the OP's data, though small, was consistent with proper case so I went with what we had been given by the OP. I actually wondered if those Wim/Lost/Draw values might be entered via a Data validation drop-down.