Count how many times a word is in a range, word can be in cell more than once. excel 2003

[dee101]

I need to count how many times the word Test is in the range B4:H9 with
Range N2 = Test the formula below works if Test is only in the cell once.
=COUNTIF($B$4:$H$9,"*" & N2 & "*")


But I have data in cells like below, this is all in one cell, so how would I have it count all the times test is in the range when some cells have test 2 or more times in a single cell?
Thanks

5
Test
8am-2pm
Test
5pm-10pm

 

[Andrew Poulsom]

Try:

=SUMPRODUCT((LEN(B4:H9)-LEN(SUBSTITUTE(B4:H9,"Test","")))/LEN("Test"))

 

[JamesW]

Nice answer Andrew. I like it... Definatly going in my box o' tricks.

 

[erik.van.geit]

Note that this is case sensitive
If "Test" "TEST" and "test" must be counted, you can use

=SUMPRODUCT((LEN(B4:H9)-LEN(SUBSTITUTE(LOWER(B4:H9),"test","")))/LEN("test"))

or refering to a cell

=SUMPRODUCT((LEN(B4:H9)-LEN(SUBSTITUTE(LOWER(B4:H9),LOWER(A1),"")))/LEN(A1))

kind regards,
Erik

 

[dee101]

Andrew, thank you very much, that did it

 

[dee101]

Erik, thanks I will keep that in mind