Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Count unique values?

    Is there a way to use the Count function to count unique values in a range? Only I have a column of dates that will occasionally have duplicates, and a cell that counts the entries in that column, and I need the count to include each date only once.

    At the moment a basic COUNT(E9:E200) did a perfectly good job until the first time a duplicate date came up, and now of course it's wrong.

    Anyone suggest a way to get it to go right again? I'd be most grateful!
    Beryl M


  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Beryl,

    Here are several possible solutions. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You need to watch the dates as they may also contain time information so may not be duplicates.

    cheers, Paul

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks, Paul and RG. I know the dates are just straight dates since I enter them, though.

    I'm fairly certain this is what I want:

    =SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2 :A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

    but it says that this "counts the number of unique text and number values in cells A2:A10 , but does not count blank cells or text values (6)" and since I get #VALUE! when I apply it to my column of dates I presume it doesn't like the dates, and I don't understand what it's doing well enough to know if it's even possible to get it to allow them or not!

    My dates are simply entered as, eg, 09 05 14 and set to display as 'Fri 9 May 14', if that's of interest.

    I is confuggulated ...
    Beryl M


  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Beryl,

    It seems you went a little overboard.

    If I understand your original post correctly this should do the trick.
    unique.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts
    So it seems, since that works perfectly! Many thanks, RG! I have to admit I just took the examples on the page you sent me to at face value, and the one that said it would count all unique text and number values whilst ignoring blanks seemed what I wanted - obviously not quite, though.

    I still don't understand quite how it does it, I don't think - my guess is that it looks at each value in the range, and if it hasn't occurred before in that range, adds one to the total? Still don't know how - there's no loop that I can see, and it looks like it's saying if its frequency is more than once, ignore it, but it can't quite be that since then that value wouldn't get counted at all, and they all have to be counted once ...

    Anyway, thanks very much again - my spreadsheet is working properly again!
    Beryl M


  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Beryl,

    I think this is an anomaly (unintended working of the function) as if you read the help file for the Frequency function it states that it has to be entered as an array formula. I did not do this.

    If you place your cursor on the formula then select from the Ribbon: Formulas->Evaluate Formula and then step through the formula you can see what happens.
    Frequency creates an array within the IF() part of the function that would look like this in my posted example {2;0;3;0;2;1;0;0;1;0}.
    You'll notice there are 10 entries ( I would think there should only be 9 but I'm sure there is a reason for the 10th one) one for each of the cells in the range.
    The 1st entry shows that there are 2 entries for 5/9/14.
    The 2nd entry shows 0 since it is a duplicate and already counted.
    You can follow this logic down the line, with the exception of that extra one on the end.

    Take the next step in the evaluation and the entries are converted to True/False values where 0=False anything else = True (normal Excel behavior).

    The next step compares each entry in the array to see if it is greater than 0 and if so changes it to a 1 so you get {1;FALSE;1;FALSE;1;1;FALSE;FALSE;1;FALSE}.
    You could make this clearer by changing the formula to full IF syntax as: =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1,0))
    Which would yield {1;0;1;0;1;1;0;0;1;0} which should make things a little clearer.

    Finally, the 1's are summed giving you the answer.

    I hope this makes it a bit clearer.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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