Counting text values

[Mr.Ribit]

I need a bit of quick help with this one:

I have a column of text values, states, and I want to be able to count
how many times each state is represented in the column. I had a
formula working but it can not handle 50 variables. What is the best
approach to this? Thank you.

Thomas

 

[Scott Johnson]

If you can insert a column, put the number 1 in that column for each
entry. Somewhere on that sheet or another open the Consolidate
Function and you will have the total for each state's occurrence in a
separate table.

 

[Dave]

Since you have 50, you may want to make a list on your spreadsheet of
all the states, then use a countif formula that points to the text
column and the state, and just copy the formula down or across to
cover all the 50 states.

e.g.
A B
'ALASKA +Countif($a$50:$a$5000,$a1) then copy this down..
'ARIZONA +Countif($a$50:$a$5000,$a2)
'CALIFORNIA +Countif($a$50:$a$5000,$a3)

The puter I am on doesn't have excel so im not sure that it will be
able to look up text in that way, since countif formulas counting text
should have quotes around the text being searched for, but it may
'insert' them automatically since the reference cell is a text cell.

Hope it starts you in the right direction.