Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Count unique text values based on condition in another column

This is a discussion on Count unique text values based on condition in another column within the Excel Questions forums, part of the Question Forums category; Hello, I need to count uniques text values in a column that contains names. But I only need to count ...

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    5

    Default Count unique text values based on condition in another column

    Hello,

    I need to count uniques text values in a column that contains names.
    But I only need to count the unique values that are satisfying a condition in another column.

    Example:

    GroupName
    aName 1
    aName 1
    aName 2
    aName 2
    bName 1
    bName 2
    bName 3
    cName 4


    So I need to count unique names from group "a"

    I already got the formula for counting the unique values from the whole list, and I just need to add the condition that would restrict the search only to one group (condition).

    Any ideas?

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,974

    Default Re: Count unique text values based on condition in another column

    Try...

    =SUM(IF(FREQUENCY(IF(A2:A100="a",IF(B2:B100<>"",MATCH("~"&B2:B100,B2:B100&"",0))),ROW(B2:B100)-ROW(B2)+1),1))

    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges, accordingly.
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    5

    Default Re: Count unique text values based on condition in another column

    Thank you,
    it's working perfectly

    ...

    now for the most difficult part...
    i have a period of x weeks (4 or 5 each month)
    I have to find the maximum number of unique names in one week

    Example:
    Group A:
    - week1 = 2 unique names
    - week2 = 1 unique name
    - week3 = 1 unique name
    - week4 = 1 unique name
    Max = 2 unique names/week


    Group B:
    - week1 = 1 unique name
    - week2 = 3 unique names
    - week3 = 1 unique name
    - week4 = 0 unique names
    Max = 3 unique names/week


    Groupweekname
    aw1Name 1
    aw1Name 2
    aw2Name 3
    aw3Name 4
    aw4Name 5
    bw1Name 1
    bw2Name 2
    bw2Name 3
    bw2Name 4
    bw3Name 5


    How do I get to this result ?
    I've found some D functions, but they only work with numbers...



    Thank you...
    I hope I've provided an interesting challenge for all of you...

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,974

    Default Re: Count unique text values based on condition in another column

    Assuming that A2:C11 contains the data, G2 contains "a", and G3 contains "b", try the following...

    D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =SUM(IF(FREQUENCY(IF($A$2:$A$11=A2,IF($B$2:$B$11=B2,IF($C$2:$C$11<>"",MATCH("~"&$C$2:$C$11,$C$2:$C$11&"",0)))),ROW($C$2:$C$11)-ROW($C$2)+1),1))

    H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =MAX(IF($A$2:$A$11=G2,$E$2:$E$11))

    Actually, in order to reduce the number of calculations that need to take place, try the following instead...

    D2, copied down:

    =A2&B2

    E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =IF(ISNA(MATCH(D2,$D$1:D1,0)),SUM(IF(FREQUENCY(IF($A$2:$A$11=A2,IF($B$2:$B$11=B2,IF($C$2:$C$11<>"",MATCH("~"&$C$2:$C$11,$C$2:$C$11&"",0)))),ROW($C$2:$C$11)-ROW($C$2)+1),1)),"")

    H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =MAX(IF($A$2:$A$11=G2,$E$2:$E$11))
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    5

    Default Re: Count unique text values based on condition in another column

    Thank you, it works wonderfully...


    I guess that your excel experience is in the region of millions of hours...

    Let me know if you need more challanges...


  6. #6
    New Member
    Join Date
    Nov 2013
    Posts
    4

    Exclamation Re: Count unique text values based on condition in another column

    Quote Originally Posted by Domenic View Post
    Try...

    =SUM(IF(FREQUENCY(IF(A2:A100="a",IF(B2:B100<>"",MATCH("~"&B2:B100,B2:B100&"",0))),ROW(B2:B100)-ROW(B2)+1),1))

    ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges, accordingly.
    I've used this formula but i"m getting an #N/A error. When i evaluate the formula it seems to be in the Match(True,#N/A,0) area.
    Since I don't exactly understand the "~" purpose in the match function I'm not sure how to fix this. Please help.

    What is strange when I clidk on the Fx beside the formula bar the result displays there but in the cell i get #N/A



    this is exactly what I have after adjusting my ranges:


    The values in Q are text (City) and values in D are numbers (customer number) , not sure if that makes a difference. I'm looking to count # of customers in a given city, but the customer number appears many times, depending on how many times delivery took place so I need a count of the unique customer number for each city.

    =SUM(IF(FREQUENCY(IF(Calculations!Q3:Q1000=Output!B19,IF(Calculations!D3:D1000<>"",MATCH("~"&Calculations!D3:D1000,Calculations!D3:D1000&"",0))),ROW(Calculations!D3:D1000)-ROW(D3)+1),1))

    Thank you for your help.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,681

    Default Re: Count unique text values based on condition in another column

    Quote Originally Posted by martyna View Post
    I've used this formula but i"m getting an #N/A error. When i evaluate the formula it seems to be in the Match(True,#N/A,0) area.
    Since I don't exactly understand the "~" purpose in the match function I'm not sure how to fix this. Please help.

    What is strange when I clidk on the Fx beside the formula bar the result displays there but in the cell i get #N/A



    this is exactly what I have after adjusting my ranges:


    The values in Q are text (City) and values in D are numbers (customer number) , not sure if that makes a difference. I'm looking to count # of customers in a given city, but the customer number appears many times, depending on how many times delivery took place so I need a count of the unique customer number for each city.

    =SUM(IF(FREQUENCY(IF(Calculations!Q3:Q1000=Output!B19,IF(Calculations!D3:D1000<>"",MATCH("~"&Calculations!D3:D1000,Calculations!D3:D1000&"",0))),ROW(Calculations!D3:D1000)-ROW(D3)+1),1))

    Thank you for your help.
    The formula, confirmed with control+shift+enter, should work as you have it.

    Do you have perhaps #N/A in Calculations!Q3:Q1000 and/or in Calculations!D3:D1000 due to a look up formula?
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    New Member
    Join Date
    Nov 2013
    Posts
    4

    Default Re: Count unique text values based on condition in another column

    Quote Originally Posted by Aladin Akyurek View Post
    The formula, confirmed with control+shift+enter, should work as you have it.

    Do you have perhaps #N/A in Calculations!Q3:Q1000 and/or in Calculations!D3:D1000 due to a look up formula?
    Thanks for the reply,
    I checked that and adjusted the ranges to be exact and not including blanks... etc, and it does not make a difference, I still get N/A.
    I couldn't figure it out so I just added a column and did a simple comparison to the row above resulting in a 1 or 0 value, so this way I just sum up the 1s in that column to get the unique number, but it would have been nice to have the above formula work.

    Thanks,
    Martyna

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,681

    Default Re: Count unique text values based on condition in another column

    Quote Originally Posted by martyna View Post
    Thanks for the reply,
    I checked that and adjusted the ranges to be exact and not including blanks... etc, and it does not make a difference, I still get N/A.
    I couldn't figure it out so I just added a column and did a simple comparison to the row above resulting in a 1 or 0 value, so this way I just sum up the 1s in that column to get the unique number, but it would have been nice to have the above formula work.

    Thanks,
    Martyna
    Having blanks in the relevant ranges should not be a problem. Can we that comparison formula that yieds a 1 or 0?
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,681

    Default Re: Count unique text values based on condition in another column

    Quote Originally Posted by Aladin Akyurek View Post
    Having blanks in the relevant ranges should not be a problem. Can we that comparison formula that yieds a 1 or 0?
    Can we... --> Can we see...
    Assuming too much and qualifying too much are two faces of the same problem.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com