Finding text
 

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

Finding text
 

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

array-enter it = CTRL+SHIFT+ENTER

Finding text
 

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*")


"Jarek Kujawa" wrote:

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

array-enter it = CTRL+SHIFT+ENTER

Finding text
 

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....

"Teethless mama" wrote in message
...
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*")


"Jarek Kujawa" wrote:

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

array-enter it = CTRL+SHIFT+ENTER

Finding text
 

yep, sorry