Countif on imported data

K

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?
 
P

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
 
S

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:
 
B

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
 
N

NBVC

Maybe your account numbers are formatted as text?

Select the Account number column and go to Data|Text to Columns, the
just click Finish
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top