Count items on the list

D

dannyboy213

On column A, it is a list of different states (ie. CA, FL , NV and so
on). I don't know how many different states in the column because the
list is too long.

Is there a formula to know how many instances for each state on the
list.
 
P

poloboyUK

Have you tried fully utilising the COUNT function.

Just spit-balling but COUNT("Ca") for example. Does that work or not?
 
D

dannyboy213

The problem is I don't know which states are on the list because the
list is too long.
 
V

vezerid

dannyboy

First you need a collection of all the different states appearing, i.e.
a list in which each state appears only once. To do this, select the
column, and use Data | Advanced Filter. Choose Fileter in another
location, Unique records only and specify the other location. No need
for criteria.

Once you have all the unique data neatly in place (say in E2:E52), next
to them, say in F2, you can put the following formula:

=COUNTIF(A:A, E2)

Assuming original list is in column A:A.

HTH
Kostis Vezerides
 
B

Bob Phillips

Just list the 50 possible somewhere, say M1:M50, and use

=COUNTIF(A:A,M1)

and copy down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"dannyboy213" <[email protected]>
wrote in message
 
V

vezerid

In my answer above I give you a way to generate the unique list of
states using Advanced Filter.

HTH
Kostis Vezerides
 
Top