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

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

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

--
Jacob (MVP - Excel)


"Eric" wrote:

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

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)


--
Regards
Roger Govier

Eric wrote:
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

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?

--
Regards
Roger Govier

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

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

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)

--
Jacob (MVP - Excel)


"Roger Govier" wrote:

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?

--
Regards
Roger Govier

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

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

.