# Excel 97: Counting Game Winners

Posted by co on June 06, 2001 10:14 PM

I have data in this form

Gm Team Final
1 Bos 1
1 Bal 2
2 Bos 3
2 Bal 0
3 Bos 1
3 Bal 2
4 Tam 4
4 Bos 11

and I want to count the number of wins Boston (Bos) has, where the winner is the team with the highest Final score in each pair of games (Gm). Note that the second team in a pair is the home team, and naturally this varies with the schedule. In this example, Boston won games 2 and 4, so the Count should = 2.

Thanks,
co

Posted by lenze on June 07, 2001 6:48 AM

Perhaps you should consider re-arranging your table as follows:

Game #: Visitor: Vis Score: Home: H Score in columns A through E: Now in column F you could enter a formula to return the name of the winner for that game. From there, you could use a Pivot Table or a countif formula to total wins by each team

Posted by Mark W. on June 07, 2001 7:33 AM

Let's assume that your data (below) has been
entered into cells A1:D9. I suggest that
enter the formula...

=(C2*2-SUMIF(\$A\$2:\$A\$9,A2,\$C\$2:\$C\$9)>=0)+0

...into cell D3, and Copy down to cell D9.

Now, you can create a summary PivotTable with
'Winner' in its DATA area, 'Gm' in its ROW area,
and 'Team' in its COLUMN area. This PivotTable
will provide win-loss summary of the entire
season.

Please let me know if you need further
clarification.

Posted by Mark W. on June 07, 2001 8:55 AM

Minor fix...

The 'Winner' formula should be...

=(C2*2-SUMIF(\$A\$2:\$A\$9,A2,\$C\$2:\$C\$9)>0)+0

Posted by co on June 07, 2001 3:13 PM

Re: Minor fix...

Okay, I missed this modification the first time
around and I was wondering.

I did figure out another way to do this. Before I
get to that, let me note that for simplicity's
sake, I left out some data. I realize, now, that
I should have included it.

My data contains inning-by-inning scores that I
copy and paste, so I don't want to change the
layout of my workbook and I'm trying to avoid
pivot tables.

Boston 000 100 000 00 1
Baltimore 000 001 000 01 2

I wrote a macro for my text editor (NoteTab) to
insert a ";" before each numeral and to strip the
blank spaces. Now, I can use Text to Columns. I'm
tracking inning sums, too.

Anyway, putting this formula in cell D2, leaving
cell D3 blank, and copying D2 and D3 down gives me
the winning team:

=IF(C2>C3,B2,B3)

Now, I can use this array formula to count wins:

{=COUNTIF(D2:D9,"Bos")}

Losses become the count of a column/2 - Wins.

Also, I should be able use the fact that the home
team is always in an odd-numbered row (or even,
depending on where I start) to count Road and Home
Wins.

Thanks for taking the time to respond to my
problem. Although I'm not using your solution, I
got on track while trying to implement it.

Cliff

Posted by co on June 07, 2001 3:17 PM

As I posted in response to Mark W., I have reasons
for not wanted to rearrange my spreadsheet or use
pivot tables. I finally developed a viable
solution using countif.

Cliff

Posted by Mark W. on June 08, 2001 7:19 AM

Re: Minor fix...

> {=COUNTIF(D2:D9,"Bos")}

FYI, COUNTIF() doesn't need to be entered as an
array formula. Okay, I missed this modification the first time

Posted by co on June 08, 2001 9:49 AM

Re: Minor fix...

FYI, COUNTIF() doesn't need to be entered as an

Thanks for pointing this out. Even though I have
been using Excel for years, I've just recently
discovered array formulas and I'm still working
my way around them.

Cliff