If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to count the number of text?
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 |
#2
|
|||
|
|||
How to count the number of text?
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 |
#3
|
|||
|
|||
How to count the number of text?
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 |
#4
|
|||
|
|||
How to count the number of text?
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 & "*") |
#5
|
|||
|
|||
How to count the number of text?
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 & "*") . |
Thread Tools | |
Display Modes | |
|
|