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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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)
 
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
 
Back
Top