Count unique values based on criteria in different column
Results 1 to 3 of 3

Thread: Count unique values based on criteria in different column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Posts
    380
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Count unique values based on criteria in different column

    Hello.

    I've in column D a lot of values with duplicates.

    I've to count how many unique values in column D in relation to the value in column K same row.

    For example: I need to know how many unique values in column D when column K = ABC12345

    Thank's.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,826
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Count unique values based on criteria in different column

    Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

    =SUM(IF(FREQUENCY(IF(K2:K10="ABC12345",IF(LEN(D2:D10)>0,MATCH(D2:D10,D2:D10,0))),ROW(D2:D10)-ROW(D2)+1)>0,1))

    Adjust the ranges accordingly.

    Hope this helps!

  3. #3
    Board Regular
    Join Date
    Sep 2017
    Posts
    380
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count unique values based on criteria in different column

    Quote Originally Posted by Domenic View Post
    Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

    =SUM(IF(FREQUENCY(IF(K2:K10="ABC12345",IF(LEN(D2:D10)>0,MATCH(D2:D10,D2:D10,0))),ROW(D2:D10)-ROW(D2)+1)>0,1))

    Adjust the ranges accordingly.

    Hope this helps!
    I'm going to try.

    Anyway, perhaps in VBA is easier.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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