+ Reply to Thread
Results 1 to 4 of 4

Need count of unique text values in column

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Question Need count of unique text values in column

    I need a formula that counts all the clients that are assigned to Hannah (only 2 in this scenario). Since this document is a list of all individual purchases, there are duplicates. I can count the unique clients with the following formula:
    =SUMPRODUCT((B2:B10<>"")/COUNTIF(B2:B10,B2:B10&""))
    ... but I need it to look up and count only the clients which belong to Hannah.

    Help?


    A B C
    1 Account Manager Client Name Purchased Item
    2 Hannah Jerry 7008
    3 Mark George 9980
    4 Hannah Jerry 9980
    5 Mark Sara 1367
    6 Mark Leo 9980
    7 Hannah Deb 1367
    8 Mark Leo 2045
    9 Hannah Jerry 2045
    10 Samantha Paul 1367
    Last edited by kitpierce; 07-21-2017 at 01:00 PM.

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Need count of unique text values in column

    Try this:
    =SUMPRODUCT((A2:A10="Hannah")/COUNTIF(B2:B10,B2:B10&""))

    or perhaps:
    =SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10<>"")/COUNTIF($B$2:$B$10,$B$2:$B$10&""))

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 07-20-2017 at 10:32 PM.

  3. #3
    Registered User
    Join Date
    09-21-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Need count of unique text values in column

    This worked. Thanks so much!

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Need count of unique text values in column

    You're most welcome, glad I could help. If you have your answer, please go to Thread Tools up top and mark your thread as SOLVED. Thanks!-Lee

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to count unique distinct values from a column where values in other column exists ?
    By Daniel Tou in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-19-2017, 05:48 PM
  2. Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  3. [SOLVED] Count unique text values within a range based on another column
    By Sebastes in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-14-2014, 01:38 PM
  4. Replies: 12
    Last Post: 07-03-2012, 04:50 PM
  5. Replies: 5
    Last Post: 05-31-2012, 08:48 AM
  6. Count Unique Values In One Column Basis Unique Values in Another Column
    By shez_raz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-07-2012, 01:55 AM
  7. Replies: 17
    Last Post: 08-24-2009, 08:58 AM

Tags for this Thread

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