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

 

[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 [email protected]...

 

[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

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]

Ok, guys.

Problem solved!
Thanks again, have a nice day!