Countif formula problem
 

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

Countif formula problem
 

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


"Robert Smith" wrote in message m...
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

Countif formula problem
 

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" wrote:

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

Countif formula problem
 

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


"Dave" wrote in message
...
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" wrote:

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