+ Reply to Thread
Results 1 to 14 of 14

count unique values based on unique values

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    U.K.
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question count unique values based on unique values

    My Data looks like
    Clients ID Fee Branch
    A0001 125 SW
    A0001 100 SW
    A0002 200 NW
    A0003 300 SW
    A0004 555 NW
    A0001 567 SW
    A0002 456 NW
    A0005 246 SW
    A0005 876 SW
    A0007 345 NW

    I am looking for a formula that can be count unique clients in each of the branches.

  2. #2
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: count unique values based on unique values

    use this CSE formula =SUM(IF($C$2:$C$101=C2,1/COUNTIF($A$2:$A$101,$A$2:$A$101)))

  3. #3
    Registered User
    Join Date
    10-15-2012
    Location
    U.K.
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: count unique values based on unique values

    doesn't seem to work. I tried this but the result is 0. Also I want to give an argument in the formula.

    For E.g. say if I like to count all the unique clinets in SW branch how do I do it?

  4. #4
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: count unique values based on unique values

    Hi
    pls find the attached example file

    Rgds
    Sadath
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-15-2012
    Location
    U.K.
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: count unique values based on unique values

    It works. Thankyou. But when I tried copying the formaula across to a new sheet its returning a decimal value.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: count unique values based on unique values

    This formula is an array formula and is not entered in the normal way (i.e just hit ENTER). That's what Sadath meant when he said it was a CSE formula.
    You need to use CNTRL + SHFT + ENTER. When you do that, you'll see {} appear around your formula.
    Go to D2 and place your cursor inside the formula bar, then hit CNTRL SHFT ENTER.
    Now you can drag that down.
    Did that help?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Registered User
    Join Date
    10-15-2012
    Location
    U.K.
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: count unique values based on unique values

    Always wondered why nobody thought of a simple function like "countu" to count unique values. This is absolutely brilliant. Thankyou. Much appriciated..

    Wondering if you would help me in adding another layer of condition in the data attached.

    Say if I have to count all unique clients in any branch who started before say a particular date, would this work. I tried to use 'AND' function with 'if', didn't work..
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-19-2012
    Location
    Rockwall Texas
    MS-Off Ver
    2010
    Posts
    3

    Unhappy Re: count unique values based on unique values

    Excellent help for me on this issue but running into one problem that you may have remedied:

    If I need to reference the value below (shown as "B2, from another column (like X2), I get "0" value

    =SUM(IF($B$2:$B$5000=B2,1/COUNT IF($K$2:$K$5000,$K$2:$K$5000)))

    Is there not a way to reference the value in another column???

    Example B.xlsx

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: count unique values based on unique values

    What about an pivot table.

    See the file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  10. #10
    Registered User
    Join Date
    10-19-2012
    Location
    Rockwall Texas
    MS-Off Ver
    2010
    Posts
    3

    Unhappy Re: count unique values based on unique values

    Excellent help for me on this issue but running into one problem that you may have remedied:

    If I need to reference the value below (shown as "B2, from another column (like X2), I get "0" value

    =SUM(IF($B$2:$B$5000=B2,1/COUNT IF($K$2:$K$5000,$K$2:$K$5000)))

    Is there not a way to reference the value in another column???

    Attachment 188581

  11. #11
    Registered User
    Join Date
    10-19-2012
    Location
    Rockwall Texas
    MS-Off Ver
    2010
    Posts
    3

    Re: count unique values based on unique values

    Would Prefer to use the formula, because I am building a Dashboard and it would all more flexibility. If this Sum(if.. will not work without using a cell within the array, then okay, but it seemed to be within reason to be able to.

    thanks anyway for your help. appreciate your effort.

  12. #12
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: count unique values based on unique values

    @neetu.aggarwal:

    see if the attached works for you.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  13. #13
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: count unique values based on unique values

    @davidnelson:

    you should create your own thread instead of hijacking others'. anyway, see if the attached works for you.

    your data have some issues - the BUYER column has numbers in TEXT format, where as, in the small tableau where you are trying to create a summary table, BUYER is in NUMBER format. this kind of mishmash is recipe for disaster.

  14. #14
    Registered User
    Join Date
    10-15-2012
    Location
    U.K.
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: count unique values based on unique values

    Thank you..just a perfect reply for my querry.

    Cheers!!

+ 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