Countif formula problem

[Robert Smith]

Hi folks,

Am doing a web query to pull down data. Worksheet can vary between 6 to 600 rows. Am trying to do a count of similar text names in column A. Text name to search is in cell J7. Download data will start on row 6.

I've tried =COUNTIF(A:A,J7) and get a result of zero. I've tried =COUNTIF(A$6:A$600,J7) and get a result of zero.

What am I doing wrong?

TIA,

Bob

 

[T. Valko]

Am doing a web query

You might be pulling in unseen characters like char 160.

Tom might actually be _Tom_ where the underscores are the unseen characters.

There is a macro at this site that will clean these unseen characters:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Biff
Microsoft Excel MVP


Hi folks,

Am doing a web query to pull down data. Worksheet can vary between 6 to 600 rows. Am trying to do a count of similar text names in column A. Text name to search is in cell J7. Download data will start on row 6.

I've tried =COUNTIF(A:A,J7) and get a result of zero. I've tried =COUNTIF(A$6:A$600,J7) and get a result of zero.

What am I doing wrong?

TIA,

Bob

 

[Dave]

Hi,
One reason, since you're pulling data from the web: it may contain spaces at
the beginning or end of the data, including Character 160.
The Trim function will get rid of most of these, but you'll need to address
the Character 160 issue separately.
Something like this in B6, copied down:
=TRIM(SUBSTITUTE(A6,CHAR(160)," "))
Then try COUNTIF on Column B
Regards - Dave.

 

[Robert Smith]

Thanks for the replies Biff & Dave ... Both solutions work and Character 160
was included, but while reading other threads about Countif problems, I
found one solution about creating a pivot table, which seems to work quite
nicely as well.

Regards,

Bob