How can I get Excel to calculate and count Wins and Losses?

Question

I am doing a project in which I am compiling the historic results of college bowl games. In Column A I have the year, B is the winner with their score in C, and D and E have the loser and their score. This is repeated across the sheet for each bowl, with cells 1B:1E merged to hold the name of the bowl. On another sheet, I have the names of the schools as well as three columns for Wins, Losses, and Ties. What formula can I enter into the sheet with all the school names on it so that I can easily fill it down and calculate the number of Wins, Losses, and Ties?

Answer 1

That's a fairly complicated problem, and I can't think of a way to do it with a single formula. I had to create two additional columns which calculate the status of each team for each game. From there I calculate wins, losses and ties for each team in separate cells. Here's how I did it:

In column A, I have team names. Column B has their scores. Column C has their opponent name. Column D has the opponent score.

In Column E, I have this formula copied down through the column:
=IF(B2>D2, A2&"Win", IF(B2
In Column F, I have this formula copied down through the column:
=IF(B2>D2, C2&"Loss", IF(B2
Then on another sheet I have the teams listed in column A with Wins calculated in column B using this function starting in row 2:
=COUNTIF (Sheet1!E$2:Sheet1!F$16, A2&"Win")

Losses are calculated in column C using this function:
=COUNTIF (Sheet1!E$2:Sheet1!F$16, A2&"Loss")

Ties use this function in column D:
=COUNTIF (Sheet1!E$2:Sheet1!F$16, A2&"Tie")

It works well with my sample of four teams and fifteen games, and by inserting rows in Sheet1 and copying the functions in columns E and F, it should work well with many more.