Thanks:  0
Likes:  0

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

1. ## Re: How do you Count the number of unique values in a list ?

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.

Jasper

2. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by 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.

Jasper
Control+shift+enter, not just enter:
Code:
```=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.

3. ## Re: How do you Count the number of unique values in a list ?

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?
Just for the record, I’ve of course tried to ‘confirm’ the formula with Control+Shift+Enter.

4. ## Re: How do you Count the number of unique values in a list ?

Thanks a lot Aladin, It works like a charm.

So other readers: Ignorem my last comment
Originally Posted by thejazz

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?
Just for the record, I’ve of course tried to ‘confirm’ the formula with Control+Shift+Enter.
My mistake!

5. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by thejazz
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.

6. ## Re: How do you Count the number of unique values in a list ?

Originally Posted by 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.

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.

## User Tag List

#### Posting Permissions

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