Each season a schedule is set for every team. Some days have multiple games scheduled, thus two teams can either play on the same day or different days. I am interested in determining the total number of days that two teams BOTH play.
I have three columns: Date, Home Team, Away Team.
In the example below, Team A and Team D both play on Oct 2,3,4 but only Team A plays on Oct 5. So the total number of times that they both play on the same day is 3.
I have tried to find a formula that will count the number of unique days that both teams play with much difficulty. The only way I've been able to do this is by combining the values in columns "Home Team" and "Away Team" and then filtering to include only the rows with values of either "A" or "D" and then counting unique dates.
Can I use SUMProduct with IF criteria that includes either team?
<tbody>
</tbody>
I have three columns: Date, Home Team, Away Team.
In the example below, Team A and Team D both play on Oct 2,3,4 but only Team A plays on Oct 5. So the total number of times that they both play on the same day is 3.
I have tried to find a formula that will count the number of unique days that both teams play with much difficulty. The only way I've been able to do this is by combining the values in columns "Home Team" and "Away Team" and then filtering to include only the rows with values of either "A" or "D" and then counting unique dates.
Can I use SUMProduct with IF criteria that includes either team?
Date | Home Team | Away Team |
Oct 2 | A | B |
Oct 2 | C | D |
Oct 2 | F | E |
Oct 3 | A | D |
Oct 4 | B | E |
Oct 4 | C | D |
Oct 4 | A | F |
Oct 5 | E | A |
Oct 5 | C | B |
<tbody>
</tbody>