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

ronie85

Board Regular
Joined
Jan 25, 2014
Messages
96
Office Version
  1. 365
Platform
  1. Windows
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.


1697659853117.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Something like this maybe
Sample.xlsx
ABCDEFGHIJKLMNOPQR
1TeamSeasonLeague12345678910111213Result
2Aberdeen22/2351DrawWinLostDrawWinWin2
3WinWinlostWinWinWinWin4
4WinlostlostWinWinlostlost0
5WinWinWinWinWinWinWinWinWin9
Sheet1
Cell Formulas
RangeFormula
R2:R5R2=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
 
Upvote 0
I think you're trying to ascertain the current streak. If so, this oughtta do er.

MrExcel posts20.xlsx
CDEFGHIJKLMNOP
2
3Match1234567891011Currrent Streak
4OutcomeLOSSLOSSDRAWWINLOSSLOSSDRAWLOSSDRAWWINWINWIN 2
5
Sheet27
Cell Formulas
RangeFormula
P4P4=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)))
 
Upvote 0
Here is another formula that will also work...
Excel Formula:
=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(0&CONCAT(IF(r="","",0+(r="Win"))),"0",-1))))
 
Upvote 0
Here is another formula that will also work...
Excel Formula:
=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
Excel Formula:
=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(CONCAT(LEFT(r,1)),{"D","L"},-1,,1))))
 
Upvote 0
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
Excel Formula:
=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.
 
Upvote 0
Peter's formula can be made case-insensitive with one slight tweak
Excel Formula:
=BYROW(E2:Q5,LAMBDA(r,LEN(TEXTAFTER(CONCAT(LEFT(r,1)),{"D","L"},-1,1,1))))
 
Upvote 0
Peter's formula can be made case-insensitive with one slight tweak
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top