Unique count formula?

[kins]

I need a formula that does a unique could, in a sense. For example:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
A1 Smith123
A2 Smith123
A3 Barker123
A4 Barker123
<o> </o>
Formula here (a5) to calculate and bring back a figure of 2 – because there are only 2 unique values.
I know how to use a unique filter but I need a formula to count the number of cells.
<o> </o>
I hope I’ve explained myself OK.
<o> </o>
Thanks in advance.
<o> </o>
Kins
Kins

 

[Aladin Akyurek]

I need a formula that does a unique could, in a sense. For example:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
A1 Smith123
A2 Smith123
A3 Barker123
A4 Barker123
<o></o>
Formula here (a5) to calculate and bring back a figure of 2 – because there are only 2 unique values.
I know how to use a unique filter but I need a formula to count the number of cells.
<o></o>
I hope I’ve explained myself OK.
<o></o>
Thanks in advance.
<o></o>
Kins
Kins

Control+shift+enter, not just enter:

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

Most alternatives are mentioned here:

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

 

[Weaver]

try

=COUNT(FREQUENCY(A1:A4,0))

where B1 is a reference to a blank cell

edit - doesn't work - thought it was too good to be true.

 

[kins]

thanks chaps, as always i appreciate it.

 

[Aladin Akyurek]

thanks chaps, as always i appreciate it.

You are welcome. Thanks for providing feedback.