Counting the number of unique days that two teams play concurrently over a season.

[MDBALL]

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?

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>

 

[liveinhope]

Keep the Dates in A, but put all the team codes in a single column .(make an extra col to show H or A if you like)

Put the 2 team codes you want to check for in F1 and G1

formula IN d2 =COUNTIFS($A$2:$A$19,$A2,$B$2:$B$19,$F$1)*COUNTIFS($A$2:$A$19,$A2,$B$2:$B$19,$G$1)*(B2=$F$1) adjust the row numbers in accordance with how many row in your data

total in D is the answer

Date	teamm	H or A	both play ?		A	D
Oct-02	A	H	1
Oct-02	C	H	0
Oct-02	F	H	0
Oct-03	A	H	1
Oct-04	B	H	0
Oct-04	C	H	0
Oct-04	A	H	1
Oct-05	E	H	0
Oct-05	C	H	0
Oct-02	B	A	0
Oct-02	D	A	0
Oct-02	E	A	0
Oct-03	D	A	0
Oct-04	E	A	0
Oct-04	D	A	0
Oct-04	F	A	0
Oct-05	A	A	0
Oct-05	B	A	0

<colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="64" style="width: 48pt;" span="2"> <col width="188" style="width: 141pt; mso-width-source: userset; mso-width-alt: 6875;" span="2"> <col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

 

[barry houdini]

I'm assuming that no team will play twice on the same day....

If that's the case then you can use this "array formula" without changing your layout

=SUM(IF(FREQUENCY(IF((B2:B100={"A","D"})+(C2:C100={"A","D"}),A2:A100),A2:A100)>1,1))

confirmed with CTRL+SHIFT+ENTER

Assumes valid dates in A2:A100