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

[Guest]

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?

 

[Bob Phillips]

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)

 

[Biff]

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