Countif and Hlookup

Hi,

I have a hlookup function in column A, extracting ID's from another sheet in specified rows. This works fine. function shown below

A1: =HLOOKUP("ID";'sheet2'!$1:$968;2;FALSE)

This returns
A1: AT

In column B I want to count the number of occurences of the result from A1 in a certain column in sheet2, so I figured I could use countif

B1: =COUNTIF('sheet2'!R$2:R$1000;A1)

This, however, returns
B1: 0

If I insted write out
B1: =COUNTIF('sheet2'!R$2:R$1000;"AT")
Excel returns,

B1: 4

Any ideas to why this is and how to fix it?

Any help is very much appreciated

regards,
Kbka

Perhaps the AT in A1 has a space at the end of it which you can't see. You can check this by putting =LEN(A1) in a blank cell, which will tell you the number of characters in A1. You can use TRIM( ... ) around your formula in A1 to remove any excess spaces.

Hope this helps.

Pete