Excel 97: Counting Game Winners

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

Let's assume that your data (below) has been
entered into cells A1:D9. I suggest that
you add a 'Winner' column header in cell D2,
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.

Minor fix...

The 'Winner' formula should be...

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

...Sorry about that!

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

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.

Thanks for your help.
Cliff

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

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