Recording Win Loss Results

[JO505]

In the attached file I want to produce a listing of the win-loss record by season, the win-loss record by win streak and a display of the year (season) record for each year.

I have a formula that will return the overall record but I am having problems breaking up the results into their respective categories.

Jim O

[mlcb]

For Excel 2007 or higher you can try

O3
=COUNTIFS(D:D,N3,G:G,"W")&" - "&COUNTIFS(D:D,N3,H:H,"L")
copy down

For all versions

O3
=SUMPRODUCT(--($D$3:$D$200=N3),--($G$3:$G$200="W"))&" - "&SUMPRODUCT(--($D$3:$D$200=N3),--($H$3:$H$200="L"))
copy down

[JO505]

micb,

Thanks for the response. That works fine for the summary by year part. Any ideas for the win-loss by season or winning streak columns (I & K)?

Jim O

[Ace_XL]

In I3
=COUNTIFS($D$3:$D3,$D3,$G$3:$G3,"W")&" - "&COUNTIFS($D$3:$D3,$D3,$H$3:$H3,"L")

In K3
=IF(G3="",0,M2+1)
format cells as 0"-0";;

[JO505]

Ace_XL,

The formula for the season record works as desired but the winning streak record is not working properly. In the formula you have a reference to "M2+1" what does that do? I am getting results of 1 - 0 for all values.

Jim O

[Ace_XL]

Oops ..should be

In K3
=IF(G3="",0,K2+1)
copy down
format cells as 0"-0";;

[JO505]

Ace,

Thanks. That works better but.. I put the formula in cell K3 and drag it down, cells K4 & K5 return a #value error and the rest seem to be okay. Changing the W to L in cell G3 clears the #value error. I am curious as to what is happening?

Jim O

[Ace_XL]

Ok..

In K3
=IF(G3="",0,1)

In K4
=IF(G4="",0,K3+1)
Copy down

[JO505]

Ace,

Thanks for the time and input.

Another workaround solution I tried is to use the number 0 as a column header. Then use only one formula.

Again thanks.

Jim O