Countif on imported data

[Kathy - Lovullo]

I'm having a problem with what should be an easy countif formula.

I have data on multiple sheets which have been imported into excel from
another program. I want to count the number of times an account number(col
B) appears on Sheet 1 for the corresponding account number on sheet 2 (column
A).
Below is the formula I am using; however it is not working.

=COUNTIF('Sheet1'!$B$2:$B$1239,Sheet2!A2)

However, if I simply re-type the account number in sheet 1, this formula
works correctly. How can I correct the formula (or data) to have the formula
work without re-typing all my account numbers?

 

[Pete_UK]

If you have imported those account numbers, then they may have spaces
or non-breaking spaces (character code 160) in there. You can get rid
of them like this:

Highlight column B of Sheet1 and do CTRL-H (Find & Replace):

Find what: Alt-0160
Replace with: leave blank
Click Replace All,

where Alt-0160 means that you should hold down the Alt key while
typing 0160 on the numeric keypad.

Hope this helps.

Pete

 

[Stefi]

Try to re-format all of your account numbers to text format with this formula
in an empty column:
=TEXT(B2,"@")
fill it to row 1239
and copy/PasteSpecial values back to column B!

Do the same in column Sheet2!A

Regards,
Stefi


„Kathy - Lovullo†ezt írta:

 

[Bob Umlas]

it's possible the values are stored as text and shee2!a2 is a number, or
vice versa. Try this:
=COUNTIF('Sheet1'!$B$2:$B$1239,Sheet2!A2*1)
or
=COUNTIF('Sheet1'!$B$2:$B$1239,Sheet2!A2&"")
likely one of them will work!
Bob Umlas
Excel MVP