How to count the number of text?

E

Eric

There is a list of text from cell A1 to A100, I would like to count the
number of "Mary" within this range, each cell may contain a senstance, such
as Mary goes to school by bus, or John meets Mary in Library ... etc. So far,
the counter for Mary is 2.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric
 
J

Jacob Skaria

Try
=COUNTIF(A1:A100,"*Mary*")

OR with the query string in cell B1
=COUNTIF(A1:A100,"*" & B1 & "*")
 
R

Roger Govier

Hi Eric

In order to make it generic, I would put the search term - Mary - in a cell.
I used C1 in this formula

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

Roger Govier

Hi Jacob

Not enough caffeine for me yet this morning.
I was making life very complicated with my solution!!!
Yours is the correct one with the very fast Countif.

Many congratulations on gaining MVP status. Very well deserved.
Was it in January (and I missed it) or has it just happened in April?
 
J

Jacob Skaria

Thanks Roger..This happened in April.

Your approach is the correct one when you have more than one instance of
'Mary' in a cell..and the below would only count exact word match. ie;
Maryland will not be counted...Once again thanks.

=(SUMPRODUCT(((LEN(SUBSTITUTE(A1:A10," ",)))))-
SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(" " &
SUBSTITUTE(" " & UPPER(A1:A10) & " "," "," "), " " &
UPPER(B1) & " ",)," ",))))/LEN(B1)
 

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

Top