Formula for finding number of unique records in a column

Hi Ken and Robert,

I do have blank cells in my data hence both formula giving
error.  I tried a
lot to modify the formula to work in blank cells as well but
no success.
Any further help??

B Regds; Rajeev Nair




On 8/5/06, Robert McCurdy <robertwmorcon.net.nz> wrote:
>
> Sorry Ken, any blank cells will cause it to error.
>
> =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
>
> There is another solution if you do have blanks within
the range.
>
>
> Regards
> Robert McCurdy
>
> ----- Original Message -----
> From: "Rajeev Nair" <rajeevknair77GMAIL.COM>
> To: <EXCEL-GPEACH.EASE.LSOFT.COM>
> Sent: Saturday, August 05, 2006 8:47 PM
> Subject: Formula for finding number of unique records
in a column
>
>
> > Hello all,
> >
> > I am trying to find out a formula which shows
number of unique records
> in a
> > particular column.
> >
> > ColumnA
> >
> > Apple
> > Apple
> > Orange
> > Mango
> > Mango
> > Mango
> > Banana
> > Banana
> >
> > Here the answer is 4, which I need to show in A10
by using a single
> > formula.   Please note I do not want to disturb
the data, means no
> sorting,
> > no advance filter and without inserting any
additional columns.
> >
> > Thanks in advance for any help.
> >
> > Best Regards;
> > Rajeev Nair
> > Nine West, Dubai - UAE.
> >
> >
>
------------------------------------------------------------
--------------
> > The EXCEL-G list is hosted on a Windows NT(TM)
machine running L-Soft
> > international's LISTSERV(R) software.  For
subscription/signoff info
> > and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
> >                             COPYRIGHT INFO:
> > http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>
------------------------------------------------------------
--------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
> international's LISTSERV(R) software.  For
subscription/signoff info
> and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                             COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G