COUNTIF problem

[kitbanting]

I am using countif to check for the occurence of a string of text but as the
range of cells is using data from another spreadsheet it doesn't recognise
the text as text. How do I get round this? It worls in previous versions of
excel but not in 2007 for some reason.

 

[Jacob Skaria]

Do you mean a wildcard search as below with *

=COUNTIF(A:A,"*searchword*")

If this post helps click Yes

 

[kitbanting]

No,

the formula is for example "=COUNTIF(B3:B37,teams!A13)" where teams is a
list of 20 three letter values and the value in the range is for example
"=Players!B4", where the screen value is one of the 20 teams. If I replace
the value of B4 with the text value it is OK. I have been using this same
spreadsheet with Excell 2000 for years but have recently upgraded to 2007 so
I don't know if there is something new that I have missed.

 

[Jacob Skaria]

Check whether the below works

=COUNTIF(B3:B37,Trim(teams!A13))

OR

=COUNTIF(B3:B37,"*" & Trim(teams!A13) & "*")

If this post helps click Yes