Count unique values?

Topic

New Reply

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!

Reply | Quote

Replies

Beryl,

Here are several possible solutions. HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You need to watch the dates as they may also contain time information so may not be duplicates.

cheers, Paul

Reply | Quote

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 …

Reply | Quote

Beryl,

It seems you went a little overboard.

If I understand your original post correctly this should do the trick.
37115-unique
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!

Reply | Quote

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: [noparse]=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1,0))[/noparse]
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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote