Number of different values in a range
 

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

Number of different values in a range
 

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


--
HTH

Bob Phillips

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

"Lucas Lehmer"
wrote in message
news:Lucas.Lehmer.28f4em_1148652601.2249@excelforu m-nospam.com...

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

Number of different values in a range
 

=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" a
écrit dans le message de news:
...

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

Number of different values in a range
 

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"
wrote in message
news:Lucas.Lehmer.28f4em_1148652601.2249@excelforu m-nospam.com...

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

Number of different values in a range
 

Ok, guys.

Problem solved!
Thanks again, have a nice day!


--
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