Say your datalist is in A1 to A200.

In B1 enter
=A1

In B2 enter this *array* formula:

=IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK(
$A$1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B 1,$A$1:$A$200&""),0)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE entry, copy down until you get errors.

This gives you a list of the unique values in Column A.

Now, to count each value, simply enter this formula in C1:

=COUNTIF(A$1:A$200,B1)

And copy down as needed.
--

HTH,

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



"PSmith" wrote in message
...
Can anyone tell me if there is a formula that will produce a list of all the
unique text values in a range, and what their corresponding counts are? I am
already familiar with the COUNTIF formula, but it can only be used if one
already knows what all of the possible values are in the data range. If the
list of possible values is long, it would take too many individual formulas
to do them one at a time. So, if my data range is something like:

Col A.
blue
red
green
yellow
red
black
yellow
blue
red
brown
brown
orange

I am hoping I can use one formula that will produce a result that will look
like
blue 2
green 1
yellow 2
red 3
black 1
brown 2
orange 1

Any suggestions would be greatly appreciated! Thanks