+ Reply to Thread
Results 1 to 5 of 5

Count unique values based on another column

  1. #1
    Registered User
    Join Date
    12-08-2008
    Location
    Sacramento, CA US
    MS-Off Ver
    2007
    Posts
    17

    Count unique values based on another column

    Hello. I am using Excel 2007, and I'm stuck with this problem. Hopefully somebody can help.

    Please Login or Register  to view this content.
    I need to get the *unique* number of users who updated their tasks, i.e., unique number of users (from name) that have a "Yes" in Updated.

    So based on the data above, the result would be 3 (User1, 2, and 3 have tasks updated. User4 doesn't have any).

    I can figure out how to count unique users only, but not unique users who had updates.

    Thank you in advance,

    Angel
    Last edited by verdugan; 07-02-2009 at 12:23 PM. Reason: Marking it solved

  2. #2
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Count unique values based on another column

    try attached pivot table .....
    Attached Files Attached Files
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Count unique values based on another column

    Given use of XL2007 unique count can be obtained using:

    =SUMPRODUCT(--(C2:C9="Yes"),1/COUNTIFS(B2:B9,B2:B9,C2:C9,C2:C9))

    COUNTIFS is not backwards compatible with earlier versions

  4. #4
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Count unique values based on another column

    If you just want to count unique users having yes then use below array formula ......

    Array formula needs to entered by pressing Ctrl+Shift+Enter
    ={SUM(((C1:C9)="Yes")*(MATCH(IF(C1:C9="Yes",B1:B9),IF(C1:C9="Yes",B1:B9),0)=ROW(B1:B9)))}
    @DK MS Excel 2007 have lot of new things i think so but still not working on it because of compatibility but i think i should start to work on it at home .......
    Last edited by mubashir aziz; 07-02-2009 at 11:23 PM.

  5. #5
    Registered User
    Join Date
    12-08-2008
    Location
    Sacramento, CA US
    MS-Off Ver
    2007
    Posts
    17

    Re: Count unique values based on another column

    Thank you both. That's exactly the info I needed.

+ 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