sum not summing

[viper]

I have two cells that contain sorta the same formula:
=sum(d9:d104) in cell h8
=Sum(E9:E104) in cell I8

the formula in cell h8 works fine, but the formula in cell i8 doesn't do anything. I get a blank(I have zero value's turned off). But if I add each cell individually(=e9+e10+e11+e12+e13+14.....+e104) it works fine and I get the total I need.

Any clues as to why? I don't really have the formula going from e9 all the way down to e104 for that would be such a pain to type. But I did run it that way down to 16.

Thanks,

[Yogi Anand]

Hi viper:
Click on cell I8 and look in the formula bar -- that will tell you what is actually in cell I8. If what you see in the formula bar is right, then your problem is with formatting of cell I8 -- otherwise you have a problem with the formula or with cell contents in E9:E104

Regards!

[Anne Troy]

Sometimes, numbers go weird on us. By using the + sign, you could be setting them straight with Excel. Using Sum doesn't *set them straight*, but you can try this:

Put a 1 in any cell.
Select that cell and copy it.
Select all the numbers that aren't working right.
Hit Edit-Paste special, Multiply, OK.

This forces a mathematical formula on the data, which forces Excel to see it as numbers, without changing the numbers themselves.



Could be your formula, but it could be this stupid anomoly too.

[viper]

I don't know what is going on. I think I may have mentioned the wrong columns but anyway, I tried to format the cells in column c as $1.00, then I tried to format column d as $1.00 but it won't format it that way all it does is give me 1.00. When I click on the cell and look in the formula bar in column d I see 1.00 but in column c i see 1. Can I force it to format the cells the same way as in C? I've never run into this before.

thanks,

[Juan Pablo González]

In the same line as Dreamboat, select your lookalike numbers, go to Data, Text to Columns, hit finish.

Your formula should now work !

[viper]

Thanks everyone, it is working now. Sorry for the other reply, I wasn't logged in and only read one of the replies. Thanks again.

[viper]

Thanks again for your help but now I have another problem. Everytime my macro runs it messes up the cell it just inserted and I have to do Juan's suggestion in order to get the Sum to work for that cell. Any suggestions?

Here is the code I am using.
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("password")
If Not IsNumeric(TextBox1) Then
MsgBox "Need a Number"
TextBox1.SetFocus
Exit Sub
End If
Dim c
For Each c In Range("CodeRange")

Debug.Print c.Value
If c.Value = Val(TextBox1) Then
If Cells(c.Row, 4) <> "" Then
Cells(c.Row + 1, 4).Rows.EntireRow.Insert
Cells(c.Row + 1, 4) = TextBox4
Range("e9").Copy
Range("e10:e130").Select
Selection.PasteSpecial Paste:=xlFormulas
Range("e9").Select
Else
Cells(c.Row, 4) = TextBox4
End If
Exit For
End If
Next


If TextBox1.Value < 1 And TextBox1.Value > 85 Then
MsgBox "Range 1 to 85"
TextBox1.SetFocus
Exit Sub
End If
ActiveSheet.Protect ("password")
Sheets("Invoice listing").Activate
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
ActiveSheet.Unprotect ("password")
Cells(NextRow, 1) = TextBox5.Value
Cells(NextRow, 2) = TextBox4.Value
ActiveSheet.Protect ("password")
Sheets("Sheet1").Activate
Unload UserForm1
End Sub

Thanks,