+ Reply to Thread
Results 1 to 9 of 9

Count unique names only, not the number of times it appears

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Indpls, IN
    MS-Off Ver
    Excel 2010
    Posts
    14

    Count unique names only, not the number of times it appears

    I've got a set of data that I update once a month and the number of team members per team changes all the time. I'm trying to write a formula that basically says, if the date matches AND the manager name matches, count the number of team members.

    In the attached sample if A2 and B4 are found in the data set, count the number of SalesReps they have. So I'm looking at Sarah for February 2013, she has two sales reps that sold something, but Katherine appears twice, so I'm not looking for a result of 3, the correct answer is 2. How do I write the formula?

    A2 will look to the data range of A14:A23 and SarahK will look to I14:I23, but I want to count H14:23.
    Attached Files Attached Files

  2. #2
    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,917

    Re: Count unique names only, not the number of times it appears

    will something like this work for you? adjust ranges to suite...

    =COUNTIFS($I$14:$I$23,$B$4,$A$14:$A$23,$A$2)
    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

  3. #3
    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,917

    Re: Count unique names only, not the number of times it appears

    will something like this work for you? adjust ranges to suite...

    =COUNTIFS($I$14:$I$23,$B$4,$A$14:$A$23,$A$2)

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique names only, not the number of times it appears

    In J14 and copy down, use this.

    =SUMPRODUCT(($H$14:H14=H14)*($I$14:I14=I14)*(MONTH($A$14:A14)=MONTH($A$2)))

    Then to get your result in B5, use this.

    =SUMPRODUCT(($I$14:$I$23=$B$4)*($J$14:$J$23=1))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    08-01-2012
    Location
    Indpls, IN
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Count unique names only, not the number of times it appears

    That's not workings, I've tried all sorts of the Count function.

    Your formula shows the criteria that I need met, but not showing what to count. So I tried this: '=countifs('Data Set'!$A$5:$A$50759,'Summary Page'!$B$3,'Data Set'!$U$5:$U$50759,'Summary Page'!$C$5,'Data Set'!$S$5:'Summary Page'!G18') and I tried put what to count in the front of the formula too. Neither are working.

  6. #6
    Registered User
    Join Date
    08-01-2012
    Location
    Indpls, IN
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Count unique names only, not the number of times it appears

    Fotis,

    I don't understand your fomula's results. For Roy, in J15 the result is 18, I don't know what that 18 represents. The formula needs to be easy enough to duplicate every month and explainable to all the sales mgrs looking at the spreadsheet.

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique names only, not the number of times it appears

    ... So I'm looking at Sarah for February 2013
    Isn't this a condition? Result for February?

    If yes, result for Sarah is 2 and result for Roy, is 0 as his TEam has no sales in February.
    Attached Files Attached Files

  8. #8
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique names only, not the number of times it appears

    As i see you marked your thread as solved.

    Is this a new way of feedback?

    And just a note for the tech team. This thread has already 7 posts(Not inclunding this one). In the front page we see that has 5 posts.

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Count unique names only, not the number of times it appears

    Maybe use a helper column, say Column K, this can be hidden with a grouping (+/-) button

    In K2, Drag/Fill Down (This progressively counts the number of times a rep & a manager occurs together on a given date in column A
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then in B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  10. #10
    Registered User
    Join Date
    08-01-2012
    Location
    Indpls, IN
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Count unique names only, not the number of times it appears

    Quote Originally Posted by Fotis1991 View Post
    As i see you marked your thread as solved.

    Is this a new way of feedback?

    And just a note for the tech team. This thread has already 7 posts(Not inclunding this one). In the front page we see that has 5 posts.

    ________________________

    This site kept disconnecting for some reason, so I kept messing with the formulas until I found a workaround. When I was able to log back in, there weren't anymore responses after mine, at least that I saw. I marked it as solved, because I solved it. You can save the sarcasm for someone else.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1