Counting instances of the word "January" in cells B12:B80

[~§-/¥\-Ê-K~]

ok anyone know how to count the number of instances the word "January" appears in the cell range B12:B80?

 

[Paul B]

=COUNTIF(B12:B80,"January")

 

[~§-/¥\-Ê-K~]

Works great, thx =)

wow, i've posted two things on this board now, both times i got the corrrect reply within 10 mins or so =)

 

[Guest]

On 2002-02-19 10:36, Paul B wrote:
=COUNTIF(B12:B80,"January")

what if the cell only contains the word january in the middle of the text e.g "one january morning" and you want to count the number of occurances of cells containing the word january?

 

[Guest]

answered it my self. A classic example of ask the question before engaging brain *january* - the power of widcards (note as i type it is 3pm on a friday, not classic thinking time.

 

[Aladin Akyurek]

On 2002-02-28 17:35, Anonymous wrote:

On 2002-02-19 10:36, Paul B wrote:
=COUNTIF(B12:B80,"January")

what if the cell only contains the word january in the middle of the text e.g "one january morning" and you want to count the number of occurances of cells containing the word january?

Given

{"Texas";
"January";
"Jan";
"One January morning";
"January blues in January"}

in A1:A5 and

{"January"}

in B1,


[1] in C1 enter:

=IF(LEN(B1),COUNTIF(A1:A5,"*"&B1&"*"),"")

[2] in C2 enter:

=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(LOWER(A1:A5),LOWER(B1),"")))/MAX(1,LEN(B1)))

Added [2], in case you'd want to ask for that too.