SUM not Summing in Excel 1

[Shelbyville]

Anyone seen this and know the reason/fix...

Background: A list of names is imported into a blank worksheet. Then numbers are entered into columns associated with the names.

If I try to sum a column, as in =sum(b1:b200) the result is zero. (Which is visually obviously wrong.) If instead I type =b1+b2+b3+ etc..., I get a value that might be correct and is not zero.

If I then go down and retype each value in each cell of the column the sum will work.

I've taken this column of numbers and copied and pasted special (value) to another blank worksheet and same thing happens.

Only the original column of names was imported, the column of numbers was data entered.

Any ideas?

 

[Zathras]

...Only the original column of names was imported...

Are you sure your import didn't also put stuff (maybe blanks) into your number column? If so, the import may have formatted the cells as Text. In that case, even though you typed numbers, they would be stored as text (see the apostrophe before the number in the edit window?)

Try formatting your numbers column as General and see what happens.

 

[SkipVought]

Hi,

What you have is digits stored on the sheet as text. They will usually be left justified, but not necessarily.

If this happens alot on imported data, try using this procedure, first SELECTing the data that needs to be converted from DIGITS to NUMBERS and then running this macro

Sub DigitToNumber()
   For Each r In Selection
      r.Value = r.Value
   Next
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

I should have said ...Try formatting your numbers column as General [blue] before entering any numbers [/color] and see what happens....

 

[Shelbyville]

Zathras and Skip - thanks. I'll try the sub see if that helps. I did try formatting the column as number (also tried general and text); also tried selected the individual cells and reformatting them for number. Nothing except re-entry seems to do the trick.

At one point, I summed portions of the colum and then, if I summed those sums, I'd get a zero; even though the individual sub-sums would have a value.

My primary concern is since initially all that was imported was the single column, this could happen again and if the value isn't zero (in the column sum cell) we might not see that there is an error!

 

[KenWright]

You will not be able to change the format of any cell that holds a textual value simply by changing the formatting. The simplest way *usually* is to simply copy an empty cell, select the data affected, and then do Edit / Paste Special / Add. This will coerce the data back to a numeric format.

If this does not work, then you very likely have garbage inside your data (eg html non-breaking spaces in), especially if it came from a web page. You then need to clean your data using something like Dave McRitchie's TrimAll macro, which can be found at the following link.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Once you have done that, then if you still have any data that looks like text, you can now use the trick already mentioned with the empty cell, and it will now work.

Regards
Ken...................

 

[Shelbyville]

Ken,
Your process did the trick. Thanks. I've learned something new - formatting doesn't always mean formatting. Thanks

 

[KenWright]

My pleasure - Would suggest keeping a copy of TrimAll in your Personal.xls so it is always handy, especially if you regularly import data like this. Also well worth taking the time to have a good trawl round the rest of Dave's site, as he has an absolute mountain of stuff on it.

Regards
Ken.............