Finding text

[Meebers]

If I have text in a cell = "I love Excel", I want to find and count the
number of times "Excel" appears in a range. CountIF seems to want all
words. TIA

 

[Jarek Kujawa]

=SUM(IF(FIND("Excel",A1:A100,1),1,0))

array-enter it = CTRL+SHIFT+ENTER

 

[Teethless mama]

Your formula returns #VALUE! error

=SUM(IF(FIND("Excel",A1:A100,1),1,0))

Try like this:

=SUM(IF(ISNUMBER(SEARCH("Excel",A1:A100)),1))
ctrl+shift+enter, not just enter

or
=COUNTIF(A:A,"*Excel*")

 

[Meebers]

Great...seems that =COUNTIF(A:A,"*Excel*") works ok and is a smaller
formula. I see that the "*" must specify a wildcard, thats where my error
was. Tx....

 

[Jarek Kujawa]

yep, sorry