+ Reply to Thread
Results 1 to 8 of 8

Count of distinct (unique) values by day

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel for Mac 2011
    Posts
    2

    Unhappy Count of distinct (unique) values by day

    Hi all,

    Thanks in advance for any assistance you can render. I've been wrestling with this issue for the last hour, and haven't made much headway.

    I've got a spreadsheet in the following format:

    Date / Name / Date / Unique Names
    Jan1 / Sue / Jan1 / 2
    Jan1 / Bob / Jan2 / 1
    Jan1 / Sue / Jan3 / 3
    Jan2 / George
    Jan2 / George
    Jan3 / Bob
    Jan3 / George
    Jan3 / George
    Jan3 / Sue

    I want to automatically perform the count for the number of unique names on a given day, as I'm dealing with roughly 6000 entries over the past year. In other words, I'd like to count how many unique (distinct) names appear on January 1, on January 2, on January 3, and so forth.

    Again, many thanks in advance for any assistance y'all can offer!
    Last edited by velorian; 12-06-2011 at 04:28 PM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Count of distinct (unique) values by day

    Hi

    One way would be to create your own function.

    Please Login or Register  to view this content.
    Assuming that your data is in the range A1:B9, the unique dates are in the range C1:C3, then enter the formula
    Please Login or Register  to view this content.
    in D1 and copy down to D3.

    HTH

    rylo

  3. #3
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Count of distinct (unique) values by day

    Try this Array Formula, (with CTRL+SHIFT+ENTER, rather than just ENTER)

    D2, copy down.

    =SUM(SIGN(FREQUENCY(IFERROR(IF(A$2:A$10=C2,MATCH(B$2:B$10,B$2:B$10,0)),""),ROW(A$2:A$10)-ROW(A$2)+1)))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  4. #4
    Registered User
    Join Date
    12-05-2011
    Location
    Austin, TX
    MS-Off Ver
    Excel for Mac 2011
    Posts
    2

    Re: Count of distinct (unique) values by day

    Those both worked - thanks so much, guys!

    Velorian

  5. #5
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Count of distinct (unique) values by day

    Guys, I have a similar issue, but easier.... I tried the formula provided, trying to modify it for my needs, but I'm missing something. What I need to do is get a count for Column A and Column B, ignoring the duplicates in Column A.

    Column A Column B
    JC Penny Mike
    JC Penny David
    JC Penny Adam
    Best Buy Chris
    Best Buy John
    Best Buy Arnold
    Roy Rogers Walter
    Roy Rogers Sarah
    Roy Rogers Amy


    Answer should be:
    Leads (Column B) = 9 (Already have this figured out, using a simple CountA)
    Companies (Column A) = 3 (This is the bugger1!)

  6. #6
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Count of distinct (unique) values by day

    Guys, I have a similar issue, but easier.... I tried the formula provided, trying to modify it for my needs, but I'm missing something. What I need to do is get a count for Column A and Column B, ignoring the duplicates in Column A.

    Column A Column B
    JC Penny Mike
    JC Penny David
    JC Penny Adam
    Best Buy Chris
    Best Buy John
    Best Buy Arnold
    Roy Rogers Walter
    Roy Rogers Sarah
    Roy Rogers Amy


    Answer should be
    Leads (Column B) = 9 (Already have this figured out, using a simple CountA)
    Companies (Column A) = 3 (This is the bugger1!)

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Count of distinct (unique) values by day

    Hello SeanKosmann,

    Please read the forum rules, rule # 2 says,

    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

    So please start your own thread.

  8. #8
    Registered User
    Join Date
    01-25-2010
    Location
    Roanoke, Virginia
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Count of distinct (unique) values by day

    My apologies, been awhile since I've been on here, I'll create a new. Thanks!

+ 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