How do you Count the number of unique values in a list ?

[Guest]

I would like to know how many unique values I have in a given list / range.

Is there a formula to return this number. The data is generally alphanumeric.

Thanks
Sean

 

[thejazz]

Hello there,

I've visited mrexcel frequently if I had questions, never had to post, always found what I was looking for, great work here!

I know I'm bumping a really old post but I have a followup question from the original question. Can the formula be adopted so that it displays the total number of unique values form the displayed values only? In short, when I filter another column, I'd like the number to adapt to the new total skipping the hidden/filtered values.

Thanks in advance!!

Jasper

 

[Aladin Akyurek]

Hello there,

I've visited mrexcel frequently if I had questions, never had to post, always found what I was looking for, great work here!

I know I'm bumping a really old post but I have a followup question from the original question. Can the formula be adopted so that it displays the total number of unique values form the displayed values only? In short, when I filter another column, I'd like the number to adapt to the new total skipping the hidden/filtered values.

Thanks in advance!!

Jasper

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(E5,ROW(E5:E22)-ROW(E5),,1)),
     IF(E5:E22<>"",MATCH("~"&E5:E22,E5:E22&"",0))),ROW(E5:E22)-ROW(E5)+1),1))

which does effect a distinct count of the items in E5:E22, a range within an autofiltered area of data.

 

[thejazz]

Thanks Aladin,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've tried your formula but for me (I use plain Excel 2010, no ad-ons) the formula doesn't seem to work. My Excel doesn't recognize FREQUENCY, any other solutions/suggestions?<o></o>
Just for the record, I’ve of course tried to ‘confirm’ the formula with Control+Shift+Enter.

 

[thejazz]

Thanks a lot Aladin, It works like a charm.

So other readers: Ignorem my last comment

Thanks Aladin,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've tried your formula but for me (I use plain Excel 2010, no ad-ons) the formula doesn't seem to work. My Excel doesn't recognize FREQUENCY, any other solutions/suggestions?<o></o>
Just for the record, I’ve of course tried to ‘confirm’ the formula with Control+Shift+Enter.

My mistake!

 

[Aladin Akyurek]

Thanks a lot Aladin, It works like a charm.

So other readers: Ignorem my last comment

My mistake!

Glad to hear you sorted it out. Thanks for providing feedback.

 

[T. Valko]

Hello there,

I've visited mrexcel frequently if I had questions, never had to post, always found what I was looking for, great work here!

I know I'm bumping a really old post but I have a followup question from the original question. Can the formula be adopted so that it displays the total number of unique values form the displayed values only? In short, when I filter another column, I'd like the number to adapt to the new total skipping the hidden/filtered values.

Thanks in advance!!

Jasper

This array formula** will count the unique entries in the filtered or unfiltered range A2:A15...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A15)-ROW(A2),0)),MATCH(A2:A15,A2:A15,0)),ROW(A2:A15)-ROW(A2)+1),1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Assumes no empty cells within the range to be counted.