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

#### MDBALL

##### New Member
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>

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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>

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

Replies
2
Views
144
Replies
9
Views
357
Replies
19
Views
1K
Replies
2
Views
284
Replies
19
Views
327

1,219,960
Messages
6,151,163
Members
451,012
Latest member
OH650R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back