Number of different values in a range

[Lucas Lehmer]

Hello everyone,

Let's say I have a range A1:A1000, each cell containig a natural number.
For my purposes these values are OK if and only if they are all different.
Is there an easy way to count the number of different cells in a range?

Many thanks!

[Bob Phillips]

=SUMPRODUCT((A2:A1000<>"")/COUNTIF(A2:A1000,A2:A1000&""))


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Lucas Lehmer" <[email protected]>
wrote in message
news:[email protected]...
>
> Hello everyone,
>
> Let's say I have a range A1:A1000, each cell containig a natural
> number.
> For my purposes these values are OK if and only if they are all
> different.
> Is there an easy way to count the number of different cells in a
> range?
>
> Many thanks!
>
>
> --
> Lucas Lehmer
> ------------------------------------------------------------------------
> Lucas Lehmer's Profile:
http://www.excelforum.com/member.php...o&userid=34514
> View this thread: http://www.excelforum.com/showthread...hreadid=545853
>

[Ardus Petus]

=SUM(IF(COUNTIF(A1:A1000,A1:A1000)>1,1))
Array formula, to be validated with Ctrl+Shift+Enter
will return the number of dupes

HTH
--
AP


"Lucas Lehmer" <[email protected]> a
écrit dans le message de news:
[email protected]...
>
> Hello everyone,
>
> Let's say I have a range A1:A1000, each cell containig a natural
> number.
> For my purposes these values are OK if and only if they are all
> different.
> Is there an easy way to count the number of different cells in a
> range?
>
> Many thanks!
>
>
> --
> Lucas Lehmer
> ------------------------------------------------------------------------
> Lucas Lehmer's Profile:
> http://www.excelforum.com/member.php...o&userid=34514
> View this thread: http://www.excelforum.com/showthread...hreadid=545853
>

[RagDyeR]

Try this:

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

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Lucas Lehmer" <[email protected]>
wrote in message
news:[email protected]...

Hello everyone,

Let's say I have a range A1:A1000, each cell containig a natural
number.
For my purposes these values are OK if and only if they are all
different.
Is there an easy way to count the number of different cells in a
range?

Many thanks!


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile:
http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=545853

[Lucas Lehmer]

Ok, guys.

Problem solved!
Thanks again, have a nice day!