SUMIF Problem
SUMIF Problem
(OP)
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.
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.





RE: SUMIF Problem
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
RE: SUMIF Problem
There is a neat solution to this problem (and lengthy discussion) here:
http://w
Essentialy what you need to do is add zero to all the "numbers".
Doug Jenkins
Interactive Design Services
www.interactiveds.com.au
RE: SUMIF Problem
RE: SUMIF Problem
I multiply everything by one, I never thought of adding zero.