How to count the number of text?

[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

 

[Jacob Skaria]

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

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

 

[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)

 

[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?

 

[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)