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

MDBALL

New Member
Joined
Sep 17, 2014
Messages
1
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?


DateHome TeamAway Team
Oct 2AB
Oct 2CD
Oct 2FE
Oct 3AD
Oct 4BE
Oct 4CD
Oct 4AF
Oct 5EA
Oct 5CB

<tbody>
</tbody>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
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

DateteammH or A both play ?AD
Oct-02AH1
Oct-02CH0
Oct-02FH0
Oct-03AH1
Oct-04BH0
Oct-04CH0
Oct-04AH1
Oct-05EH0
Oct-05CH0
Oct-02BA0
Oct-02DA0
Oct-02EA0
Oct-03DA0
Oct-04EA0
Oct-04DA0
Oct-04FA0
Oct-05AA0
Oct-05BA0
<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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,486
Messages
5,831,968
Members
430,099
Latest member
rdhoy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top