+ Reply to Thread
Results 1 to 3 of 3

Count Distinct Names in Team Summary

  1. 07-11-2017, 10:50 AM #1
    Jay_hl
    Jay_hl is offline
    Registered User
    Join Date
    09-09-2006
    MS-Off Ver
    Excel (Office 365 ProPlus)
    Posts
    47

    Count Distinct Names in Team Summary

    All

    Got an Excel challenge which looks at counting distinct names of people in a list, against the team they work in.

    Expected output in the attached.

    Jay
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-11-2017, 11:10 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,563

    Re: Count Distinct Names in Team Summary

    Two possible solutions based on your description and attachment, which don't quite match!

    Excel 2016 (Windows) 32 bit
    F
    G
    H
    4
    		 Count of Names
    Unique Names
    5
    		 TeamA
    1
    1
    6
    		 TeamB
    2
    1
    7
    		 TeamC
    2
    1
    8
    		 TeamD
    0
    0
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    G
    H
    5
    =COUNTIF($C$5:$C$9,F5)
    =SUM(--(FREQUENCY(IF($C$5:$C$9=F5,MATCH($B$5:$B$9,$B$5:$B$9,0)),ROW(B5:B9)-ROW(B5)+1)>0))
    Sheet: Sheet1

    H5 is an array formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-11-2017, 11:15 AM #3
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Count Distinct Names in Team Summary

    Count names
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    for unique
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    v B C D E F G H
    3 Output
    4 Count of Names Unique
    5 Name1 TeamA TeamA 1 1
    6 Name2 TeamB TeamB 2 1
    7 Name3 TeamC TeamC 2 1
    8 Name3 TeamC TeamD 0 0
    9 Name2 TeamB
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    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. How to count unique text & number to have same function with distinct count in office 2013
    By FM_mine in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 04-11-2016, 11:14 PM
  2. [SOLVED] Grabbing Random Names but Needs to be Distinct
    By chad.portman in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-05-2014, 12:05 PM
  3. [SOLVED] counting how many names are only part of only one team
    By Gooford in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 04-23-2013, 08:12 AM
  4. [SOLVED] Retrieve player names from another sheet having a database of duplicate team names.
    By robertooo in forum Excel General
    Replies: 11
    Last Post: 10-21-2012, 01:40 AM
  5. Names based on team names
    By inayat in forum Excel General
    Replies: 3
    Last Post: 10-10-2011, 07:09 AM
  6. [SOLVED] How do I count distinct names?
    By Rona in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-08-2006, 10:25 PM
  7. how can I count distinct names in an excel list?
    By RPC@Frito in forum Excel General
    Replies: 5
    Last Post: 02-03-2005, 06:06 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