helllo again,

i a have a "Y" column of date entry.

Most of dates are repeated, some are entered once.

I need to count only the number of dates (excldg repetitions).

example
15-Sep-09
22-Sep-09
20-Oct-09
20-Oct-09
20-Oct-09
22-Oct-09

From above 6 dates; the unique count should be 4.

thanks

Try the below

=SUMPRODUCT((Y1:Y100<>"")/COUNTIF(Y1:Y100,Y1:Y100&""))

If cell Y1 is header then adjust the range as Y2:Y100

If this post helps click Yes
---------------
Jacob Skaria


"driller2" wrote:

> helllo again,
>
> i a have a "Y" column of date entry.
>
> Most of dates are repeated, some are entered once.
>
> I need to count only the number of dates (excldg repetitions).
>
> example
> 15-Sep-09
> 22-Sep-09
> 20-Oct-09
> 20-Oct-09
> 20-Oct-09
> 22-Oct-09
>
> From above 6 dates; the unique count should be 4.
>
> thanks
>
>
>

Try this...

=SUM(--(FREQUENCY(A2:A7,A2:A7)>0))

--
Biff
Microsoft Excel MVP


"driller2" <(E-Mail Removed)> wrote in message
news:049E569F-8B7A-4E04-A422-(E-Mail Removed)...
> helllo again,
>
> i a have a "Y" column of date entry.
>
> Most of dates are repeated, some are entered once.
>
> I need to count only the number of dates (excldg repetitions).
>
> example
> 15-Sep-09
> 22-Sep-09
> 20-Oct-09
> 20-Oct-09
> 20-Oct-09
> 22-Oct-09
>
> From above 6 dates; the unique count should be 4.
>
> thanks
>
>
>

thank you !

You're welcome!

--
Biff
Microsoft Excel MVP


"driller2" <(E-Mail Removed)> wrote in message
news:693829C6-80A9-4445-B3F9-(E-Mail Removed)...
> thank you !

thank you.
the formula u shared seems to work also for texts.

"Jacob Skaria" wrote:

> Try the below
>
> =SUMPRODUCT((Y1:Y100<>"")/COUNTIF(Y1:Y100,Y1:Y100&""))
>
> If cell Y1 is header then adjust the range as Y2:Y100
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "driller2" wrote:
>
> > helllo again,
> >
> > i a have a "Y" column of date entry.
> >
> > Most of dates are repeated, some are entered once.
> >
> > I need to count only the number of dates (excldg repetitions).
> >
> > example
> > 15-Sep-09
> > 22-Sep-09
> > 20-Oct-09
> > 20-Oct-09
> > 20-Oct-09
> > 22-Oct-09
> >
> > From above 6 dates; the unique count should be 4.
> >
> > thanks
> >
> >
> >