Microsoft Excel - Counting current streak - Asked By Renan Germano on 15-Jan-10 11:47 AM

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..


Excel: Counting current streak - mv ark replied to Renan Germano on 16-Jan-10 03:55 AM

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
Jonathan VH replied to Renan Germano on 16-Jan-10 02:19 PM

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.

Renan Germano replied to Jonathan VH on 19-Jan-10 08:25 AM


It worked perfectly!

Tks Jonathan!

Corey replied to Renan Germano on 25-Feb-10 11:39 AM
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!