SUMIF Problem

[ACTGuy]

I need to convert an .mdi file to excel. I first copied the information to a text file, then to excel. Doing this exercise allowed me to maintain the columns needed for a spreadsheet. I am using excel as a database, somewhat.
I put the converted information in a sheet. In another sheet I am pulling selected information out using “SUMIF” statements. The number format in the column I am searching has the area of the job as the first digit. The remaining digits are cost codes. The number 150515 would be area 1 and 50501 would be the cost code for safety. In another column is the cost. I use wild cards to add cost in several different areas. As I go through this exercise 20-30 times I noticed some of the requested values are zero when there should be a dollar figure. Some function properly and some do not. If I put the area digit in the formula, I receive the proper value. But that is only for that one area. I have 10 areas and want to know the total of the cost in all areas for a given cost code. I’ve tried formatting the cells. I’ve tried retyping the value. I don’t understand why the formula works on some, but not others.

 

[ACTGuy]

I fixed the problem, but don't understand why or how.
I formated all the cells the same. Then I had to open each cell, as to edit the cell. All formulas are functioning properly. I'm glad I had only 500 cells to do that to. There has got to be a better way. If you know, please reply.
Thanks

 

[IDS]

It sounds like you are suffering from numbers imported as text.

There is a neat solution to this problem (and lengthy discussion) here:

http://www.dailydoseofexcel.com/archives/2006/02/18/number-stored-as-text/#comment-32641

Essentialy what you need to do is add zero to all the "numbers".

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au

 

[ACTGuy]

Thanks IDS. That took care of it. I was regretting next week when this excercise repeated itself.

 

[umbrau44]

I do something similar when this comes up.

I multiply everything by one, I never thought of adding zero.