+ Reply to Thread
Results 1 to 4 of 4

Two columns of teams, count the number of times the teams meet?

  1. 10-03-2013, 04:33 PM #1
    tpe102
    tpe102 is offline
    Registered User
    Join Date
    05-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    2

    Two columns of teams, count the number of times the teams meet?

    TEAMS.jpg

    Hello, this seems like it would be easy but I'm stumped. I want to count how many times team 1 and team 2 meet in the attached example and populate the grid (see picture). The order doesn't matter, so Bears & Wolves is the same as Wolves and Bears, etc. thanks in advance, I hope this is sufficient explanation

    Here is the data :

    Team 1 Team 2
    Bears Tigers
    Lions Tigers
    Falcons Wolves
    Hawks Falcons
    Wolves Lions
    Tigers Hawks
    Bears Tigers
    Lions Tigers
    Falcons Wolves
    Hawks Falcons
    Wolves Lions
    Tigers Hawks
    Bears Tigers
    Lions Tigers
    Falcons Wolves
    Hawks Falcons
    Wolves Lions
    Tigers Hawks
    Reply With QuoteRegister To Reply
  2. 10-03-2013, 04:49 PM #2
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Two columns of teams, count the number of times the teams meet?

    In E2
    =SUM(SUMPRODUCT(($A$2:$A$19=$D2)*($B$2:$B$19=E$1)),SUMPRODUCT(($A$2:$A$19=E$1)*($B$2:$B$19=$D2)))

    Copy down and across
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  3. 10-03-2013, 08:03 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Two columns of teams, count the number of times the teams meet?

    Another option...

    Add a helper column (I used C) anc copy trhis down...
    =A2&B2
    Then in your table, use this, copied down and across...
    =COUNTIF($C$2:$C$19,$E2&F$1)+COUNTIF($C$2:$C$19,F$1&$E2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 10-04-2013, 09:21 AM #4
    tpe102
    tpe102 is offline
    Registered User
    Join Date
    05-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Two columns of teams, count the number of times the teams meet?

    Worked perfectly! Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. 80 teams 25 weeks teams no dups over 25 weeks
    By hzppby in forum Excel General
    Replies: 0
    Last Post: 03-11-2013, 08:19 PM
  2. [SOLVED] count the number of instances in a sheet where the team name and give teams investment
    By prodking in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-22-2012, 02:43 AM
  3. Generate 8-week schedule based on number of teams
    By JakeD in forum Excel General
    Replies: 0
    Last Post: 10-20-2011, 02:47 PM
  4. Using sumif to count win & loss where 2 teams play each other from the fixture list
    By Laurence1965 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-21-2011, 09:17 AM
  5. Excel 2007 : Reducing number of teams in Excel spreadsheet
    By KillieChris in forum Excel General
    Replies: 4
    Last Post: 11-25-2010, 03:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1