Count the number of unique values

[BrutalLogiC]

hello can you help me please

how do I count the number of unique numbers or texts in a column

A1 = 2
A2 = 3
A3 = 4
A4 = blue
A5 = 3
A6 = blue

the formula should count 4 in column A since there are 4 unique values

hope that makese sense

thanks

 

[BobUmlas]

Ctrl+shift+enter:
=SUM(1/COUNTIF(A1:A6,A1:A6))

 

[T. Valko]

hello can you help me please

how do I count the number of unique numbers or texts in a column

A1 = 2
A2 = 3
A3 = 4
A4 = blue
A5 = 3
A6 = blue

the formula should count 4 in column A since there are 4 unique values

hope that makese sense

thanks

This one will ignore empty/blank cells...

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

 

[BrutalLogiC]

thanks! I do want it to ignore the blanks

what about if I filter column B?

so in column B I had
B1 = yes
B2 = yes
B3 = no
B4 = yes
B5 = yes
B6 = yes

and I filtered by yes, i would want the answer to update from 4 unique (when unfiltered) to 3 unique when filtered by column B = yes

hope I am explaining myself!

 

[T. Valko]

thanks! I do want it to ignore the blanks

what about if I filter column B?

so in column B I had
B1 = yes
B2 = yes
B3 = no
B4 = yes
B5 = yes
B6 = yes

and I filtered by yes, i would want the answer to update from 4 unique (when unfiltered) to 3 unique when filtered by column B = yes

hope I am explaining myself!

Ok, let's assume the full unfiltered range is A3:B10.

Array entered**:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A3:A10,ROW(A3:A10)-ROW(A3),0,1)),MATCH(A3:A10,A3:A10,0)),ROW(A3:A10)-ROW(A3)+1)>0,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.

 

[Aladin Akyurek]

thanks! I do want it to ignore the blanks

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$1:$A$6<>"",MATCH("~"&$A$1:$A$6,$A$1:$A$6&"",0)),
ROW($A$1:$A$6)-ROW($A$1)+1),1))

what about if I filter column B?

so in column B I had
B1 = yes
B2 = yes
B3 = no
B4 = yes
B5 = yes
B6 = yes

and I filtered by yes, i would want the answer to update from 4 unique (when unfiltered) to 3 unique when filtered by column B = yes

hope I am explaining myself!

Control+shift+enter, not just enter:

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

By the way, here is a link on performance issues regarding formulas for unique count:

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[T. Valko]

See what I mean?

 

[BrutalLogiC]

Thanks again, my range is D4:D500 and I've got

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D4:D500,ROW(D4:D500)-ROW(D4),0,1)),MATCH(D4:D500,D4:D500,0)),ROW(D4:D500)-ROW(D4)+1)>0,1))}

seems very complicated but it works fine cheers!

 

[T. Valko]

Thanks again, my range is D4:D500 and I've got

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D4:D500,ROW(D4:D500)-ROW(D4),0,1)),MATCH(D4:D500,D4:D500,0)),ROW(D4:D500)-ROW(D4)+1)>0,1))}

seems very complicated but it works fine cheers!

Yes, it is very complicated.

For the life of me, I don't know why Miscosoft doesn't come up with more functions for dealing with filtered data.

Thanks for the feedback!