Counting unique text/number cells from a range
 

Please consider a long range of text cells containing A, B, C, D....Z
and more.

Lets say it goes from H1 to H1500

A -H1
A
B
F
D
A
H
D

Try...

=SUMPRODUCT((H1:H1500<"")/COUNTIF(H1:H1500,H1:H1500&""))

Hope this helps!

In article ,
sudeepd12
wrote:

Please consider a long range of text cells containing A, B, C, D....Z
and more.

Lets say it goes from H1 to H1500

A -H1
A
B
F
D
A
H
D

Thanks for the command. It gives the number of distinctive or unique
values in the range.

Could you suggest a way to get the count of each of the repetitive
values without having to specify each using the COUNTIF command.

For instance
-----------
jbushnel --A1
jbreen
jthayer
rrobinson
ajagmag
jthayer
jbushnel --A7
-----------

I am looking for a formula which will split the results as:

A8-- jbushnell 2--B8
jthayer 2
rrobinson 1
ajagmag 1
jbreen 1

Is this possible?Please let me know. Thanks for your time.

Regards,
Sudeep


--
sudeepd12
------------------------------------------------------------------------
sudeepd12's Profile: http://www.excelforum.com/member.php...o&userid=24309
View this thread: http://www.excelforum.com/showthread...hreadid=379169

Try...

B8, copied down:

=COUNTIF($A$1:$A$7,A8)

Hope this helps!

sudeepd12 Wrote:
Thanks for the command. It gives the number of distinctive or unique
values in the range.

Could you suggest a way to get the count of each of the repetitive
values without having to specify each using the COUNTIF command.

For instance
-----------
jbushnel --A1
jbreen
jthayer
rrobinson
ajagmag
jthayer
jbushnel --A7
-----------

I am looking for a formula which will split the results as:

A8-- jbushnell 2--B8
jthayer 2
rrobinson 1
ajagmag 1
jbreen 1

Is this possible?Please let me know. Thanks for your time.

Regards,
Sudeep


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=379169