COUNTIF help

[Markus]

I have excel 2000. I am trying to count the number of
times the word "good" appears in a selected range. I am
using this formula
=Countif(A1:A9,"good")
I keep returning a result of 0 when the word "good"
appears 4 times. The problem I've noticed is that since
cells A1 - A9 contain lookup formulas that find the
word "good" or "bad" from another sheet the COUNTIF
function does not seem to work but if I delete the
formulas in the selection and type the word manually the
COUNTIF works. Is there any way that I can count the
results of my lookup cells(A1-A9)?

Thanks
Markus

 

[RagDyer]

Try this:

=COUNTIF(A1:A9,"*good*")

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have excel 2000. I am trying to count the number of
times the word "good" appears in a selected range. I am
using this formula
=Countif(A1:A9,"good")
I keep returning a result of 0 when the word "good"
appears 4 times. The problem I've noticed is that since
cells A1 - A9 contain lookup formulas that find the
word "good" or "bad" from another sheet the COUNTIF
function does not seem to work but if I delete the
formulas in the selection and type the word manually the
COUNTIF works. Is there any way that I can count the
results of my lookup cells(A1-A9)?

Thanks
Markus

 

[Peo Sjoblom]

COUNTIF works but you have extra spaces crappy html trailing characters
try

=Countif(A1:A9,"*good*")



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Alan]

Its probably a not exact match problem. If you have as the result of the
lookup '<space>Good' or 'Good<space>' then it wont work. Try in A1:A9 going
Edit from the Toolbar > Replace > Hit the spacebar in 'Replace What', leave
'Replace With' empty and hit 'Replace All'
If you are importing data from somewhere you may have encountered the
dreaded CHR(160) which appears on the screen as a space, which is CHR(32),
but is not a space. In this event highlight the whole sheet, Edit > Replace

Hold down the left alt key and type in 0160, nothing will show in the

dialogue box, leave replace with blank and hit 'Replace All'
Good Luck!
Alan.

 

[Bob Phillips]

or

=SUMPRODUCT(--(TRIM(A1:A100)="good"))

if it must not allow for extra characters

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

or

=SUMPRODUCT(--(TRIM(A1:A100)="good"))

if it must not allow for extra characters ...

Or, since HTML nonbreaking spaces wouldn't be removed by TRIM,

=SUMPRODUCT(--(TRIM(SUBSTITUTE(RngOrArray,CHAR(160),""))="good"))