Hi everybody
I am trying to figure out a method for showing the record a team has against a certain group of teams.
In the below, I have Teams A-D with their corresponding group in a table, West (Teams A & B) or East (Teams C & D). I also have 12 mock games listed between these teams, Home Team in column A, Away Team in B, Home Score in C, and Away Score in D.
I want to show all the times team A won against the East (teams C & D). I broke it down in columns F-H where I manually examinded the results of the game and entered a yes if the instance was true. Column F is for all the games a team from the East played (8 games). G is when team A played the East teams (4 games). H is when A beat the Eastern teams (3 games). Therefore, I would like to report that total value "3" in the corresponding table.
I know there's got to be a way to automate this somehow. The larger sheet has over a thousand games so I would prefer not to have to manually count each time, especially since more data will be entered. I thought about using a sumproduct function to somehow lookup the 3 parts but that hasn't yielded any positive results so far.
Appreciate any ideas you might have!
I am trying to figure out a method for showing the record a team has against a certain group of teams.
In the below, I have Teams A-D with their corresponding group in a table, West (Teams A & B) or East (Teams C & D). I also have 12 mock games listed between these teams, Home Team in column A, Away Team in B, Home Score in C, and Away Score in D.
I want to show all the times team A won against the East (teams C & D). I broke it down in columns F-H where I manually examinded the results of the game and entered a yes if the instance was true. Column F is for all the games a team from the East played (8 games). G is when team A played the East teams (4 games). H is when A beat the Eastern teams (3 games). Therefore, I would like to report that total value "3" in the corresponding table.
I know there's got to be a way to automate this somehow. The larger sheet has over a thousand games so I would prefer not to have to manually count each time, especially since more data will be entered. I thought about using a sumproduct function to somehow lookup the 3 parts but that hasn't yielded any positive results so far.
Appreciate any ideas you might have!