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>
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,987
Members
409,614
Latest member
wile2u

This Week's Hot Topics

Top