Perplexed with Countif

  • Thread starter Thread starter Lloyd H. London
  • Start date Start date
L

Lloyd H. London

I have a column with a number of text entries, the range is B2:B7844.
One of many entries in the column is Green Valley.
To count those cells with Green Valley entered, I have used the Countif
function.
The exact formula is =countif(B2:B7844,"Green Valley").
The formula is returning a result of 0 (zero). What is wrong with my
formula. The format was originally General, and I have tried to
reformat as Text, with no change.
I am using Excel 2002.
 
You probably have either leading or trailing spaces, try

=COUNTIF(B2:B7844,"*Green Valley*")
 
And just in case there are extra spaces between the words

=SUMPRODUCT(--(ISNUMBER(FIND("Green Valley",TRIM(B2:B7844)))))

which is case sensitive

=SUMPRODUCT(--(ISNUMBER(SEARCH("Green Valley",TRIM(B2:B7844)))))

which is not
 
Excellent call. It was a space after, now fixed. I used
=left(b2,len(B2)-1) and pasted values on top of the existing data (in a
copy of the file). I then used countif and counta to make sure numbers
matched up.
Thanks
 
Back
Top