Microsoft Excel - Counting current streak

Hello! I have a table with the results of each team´s recent games. In column A are the game dates, and in each other column is a team name (B1,C1,D1..). In B2,C2,D2 I put the result for that game, either W, D or L.

How can i make a formula that gives me the current streak for each team? It should give me, for example, 2 wins or 2 losses or 3 draws..

Let´s say one teams current form is: wwwddll

In this example, I want the formula to say 2 losses.

And if this team wins the next game, it should automatically change the result to 1 win..

Regards,
RG

Use the following formula at the end of each column range after changing the cell range as appropriate -
=COUNTIF(B2:B4,"w") &" wins " & COUNTIF(B2:B4,"d") &" draws " & COUNTIF(B2:B4,"l") &" losses"

The example above takes only 2 rows of a column into consideration

There's probably an easier way to do this, but this is all I could come up with.  If your game dates are in A3:A12 (including future dates), try this for your column B team:

=IF(COUNTIF(B$3:B$12,B$3)=COUNTA(B$3:B$12),COUNTA(B$3:B$12),MAX((B$3:B$12<>"")*ROW(B$3:B$12))-MAX((B$3:B$12<>INDEX(B$3:B$12,MATCH("*",B$3:B$12,-1)))*(B$3:B$12<>"")*ROW(B$3:B$12)))&" "&LOOKUP(INDEX(B$3:B$12,MATCH("*",B$3:B$12,-1)),{"D","L","W"},{"Draws","Losses","Wins"})

That's an array formula, so, after typing or copying the formula into the cell, pressCtrl+Shift+Enter rather than just the Enter key.  If you successfully enter it as an array formula, it show curly braces surrounding the formula in the formula bar.  You can copy that formula across the columns to get the streaks for the rest of the teams.  You'll need to adjust the ranges to reflect your actual sheet layout, of course; e.g. if your dates are in A2:A30, change all the range addresses from B$3:B$12 to B$2:B$30 and that one B$3 at the beginning of the formula to B$2.

Great!

It worked perfectly!

Tks Jonathan!

I have a quick question regarding this, I have a very simlar spreadsheet, except in cloumn A, I have the names of the team, row 2, I have the dates, and in columns B5:AB5 i have the results, ie W for win, L for Loss, and T for Tie. How can I perform this streak function so if it looks like this w, w , blank cell , l , l , w.... it will return 1 win.

Thanks for the help!