How to count the number of times a string appears in a column?

I have an excel worksheet with, among other things, a column in which each
cell has a list of alpha numeric strings like, "NA998748". The column is
several hundred rows long, and each cell contains up to 10 or 15 strings. I
need to be able to run a function that tells me how many times each exact
string appears in the column, which exact one appears most, etc etc. What is
the best way to do this?

Assuming that the string you want to count is in C1, and you are searching
in column A, try

=SUMPRODUCT(--(LEN(A1:A2000)-LEN(SUBSTITUTE(A1:A2000,C1,""))))/LEN(C1)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"btsray" <[email protected]> wrote in message
news:[email protected]...
> I have an excel worksheet with, among other things, a column in which each
> cell has a list of alpha numeric strings like, "NA998748". The column is
> several hundred rows long, and each cell contains up to 10 or 15 strings.
I
> need to be able to run a function that tells me how many times each exact
> string appears in the column, which exact one appears most, etc etc. What
is
> the best way to do this?

Hi!

Exact includes being case sensitive. So, NA998748 and Na998748 and na998748
would not be matches.

A1 = NA998748

=SUMPRODUCT(--(ISNUMBER(FIND(A1,B1:B100))))

If you really didn't mean "exact" replace FIND with SEARCH. Using SEARCH,
NA998748 and Na998748 and na998748 would be matches.

Biff

"btsray" <[email protected]> wrote in message
news:[email protected]...
>I have an excel worksheet with, among other things, a column in which each
> cell has a list of alpha numeric strings like, "NA998748". The column is
> several hundred rows long, and each cell contains up to 10 or 15 strings.
> I
> need to be able to run a function that tells me how many times each exact
> string appears in the column, which exact one appears most, etc etc. What
> is
> the best way to do this?