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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting Values in a list 2
counting how many times a certain month appears 3
number times a value appears in a column beside it chronological 3
count no. of commas (,) in a string in a cell 3
Delete rows based on contents of Col A 0
Counting results of a formula 9
Excel Pivot Table - Counting Unique Values within one column of da 1
counting the number of times a word appears 5

Back
Top