Counting text in a column

P

prom4x

I used to think I was good at Excel until this stumped me.

I have a column with a variety of text in each cell.

Example:

A1
bob
bob
ted
ted
ted
charley
charley
charley
charley, etc.

Is there a formula or way I can take the whole column of data and hav
it come back with something similar to:
bob = 2
ted=3
charley=4
etc.

for all the variables in the column?

Please help, I am dying over here
 
B

Bob Phillips

In B1 add = A1
In C1 add =IF(B1="","",(" = "&COUNTIF(A:A,A1)))
In B2 add
=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with Ctrl-Shift-Enter

Copy B2 down, and C1 down.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

Gary''s Student

I would use a Pivot table. You will need a label at the top of the column,
names, for example. Just put names in the row area and count of names in the
data area.

Once you have tried a Pivot Table, you will never go back.
 
K

Ken Wright

Data / Filter / Advanced Filter - Copy to another location gets you a unique
list. COUNTIF then gets you the number of each of the elements within the
source data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
R

Ron Coderre

If you want to use a formula:

try this:
=COUNTIF(A1:A10,"BOB")
or
=COUNTIF(A1:A10,"*BOB*")

(adjust the range references to suit your situation)

You could also us a Pivot Table

Does that help?
Ro
 
P

prom4x

There is about a hundred titles in this column. Is there a way to get a
count on each title in one shot?
 
R

Ron Coderre

Then Pivot Table is the easiest way to go

Make sure there is a column heading, like "FirstName", at the top of
the data.

Data>Pivot Table
\Use Excel list
\Select your list (including the heading)
\Click the [Layout] button and
Drag labels into the pivot table
ROW: FirstName
DATA: Count of FirstName

Select where you want the Pivot Table and....there's your name count
table.

Does that help?

Ron
 
Top