Do you need the entire Column--from A1 all the way to A65536? Or, do you just
need the formula to always account for the all the entries in Column A?
I might be misremebering, but I don't believe that MATCH can accept an
entire Column as an argument.

If all the Numbers are in a contiguous range (no blanks Rows), then you can
do the following:
1. Click on cell A1 (the first cell in the range)
2. Insert | Name | Define
3. Type MyRange for the Name: (no spaces)
4. Then, in the Refers to line, type this:
=OFFSET(Sheet8!$A$1,0,0,COUNTA(Sheet8!$A:$A))
5. Then, try this formula (a modification of the one Domenic posted:
=SUM(IF(FREQUENCY(IF(myrange<"",MATCH(myrange,myr ange,0)),IF(myrange<"",MATCH(myrange,myrange,0))) 0,1,0))

Make sure you enter it with Ctrl+Shift+Enter.
You may need to copy the formula to a text editor (like Notepad) to make
sure you get it all on one line and don't have any spaces at the end of it.
Then, copy from the text editor to Excel. Double_click on the cell, and then
press Ctrl+Shift+Enter.

If you have blank rows in the data list, this formula may not return the
correct result.


tj

Here is a programmatic solution that may work for you. This macro was taken
from:
http://www.exceltip.com/show_tip/Pri...Excel/520.html

If you are new to macros, look at before using the code below:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ' add the unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function

Hope this helps,
tj

*Jarom* Wrote:
doesn't seem to be working. :(

What do you mean it doesn't work? The formula is correct and works
fine. Are you trying to reference the entire column? If so, try the
second formula I offered instead...

=SUM(IF(A1:A65535<"",1/COUNTIF(A1:A65535,A1:A65535)))

...entered using CONTROL+SHIFT+ENTER.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=320435