cannot sum or total

[deb]

running excel 2003 in xp pro. Can sum some columns, but not others. example
trying to sum b23:b27 in B28 have checked that all cells are numbers but the
sum comes to zero. doesn't matter what numbers are in column still sums to
zero.

 

[Wigi]

Did you check them with the ISNUMBER function?

What is the cell formatting of the cell with the formula?

 

[deb]

formatting is number with 0 decimal places

Did you check them with the ISNUMBER function?

What is the cell formatting of the cell with the formula?

 

[Dave Peterson]

Try:
=count(b23:b27)
in an empty cell.

If that returns 5 then your =sum() formula should work ok.

But I bet that you have text that looks like numbers:
Reformat the cells as General (or anything but text)

And then reenter the values in the cells
Since there's only 5 cells, you can:
select each cell
Hit F2 and then enter.

Then check the =count() and =sum() formulas.

There are other ways to convert text numbers to number numbers. One is:

Select an empty cell
edit|copy

Select the range to fix
edit|paste special|check add and values

============
If this stuff doesn't work, then you may have other white space in your
cells--if you've copied the data from the web, you may have those HTML
non-breaking spaces in the cells.

Select the range
edit|Replace
what: alt-0160
(hit and hold the alt key while you type 0160 on the number key pad)
with: (leave blank)
replace all

 

[RagDyeR]

Try this as a test:

=B23+B24+B25+B26+B27

If you get the right answer, your numbers are Text,
no matter what the cell format is.

Follow Dave's suggestions.

If you get an error message, you've got invisible characters mixed in with
the numeric characters.

Post back in that event.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


formatting is number with 0 decimal places

 

[ShaneDevenshire]

Hi,

Two possible solutions:
1. Press the F9 key. Does your formula update. If so, calculation mode may
be set to manual. Choose Tools, Options, Calculation, Automatic.

2. Well everybodies is telling you correctly, what problem most likely is.
So to correct it select an empty cell and choose copy. Select the cells that
are not calculating, the numbers, and choose Edit, Paste Special, Add.

You did not indicate if you had green triangles at the top right of each
cell? You did not tell us the version of Excel you are using.

 

[Don Guillett]

I can remember you to credit for this but I have it on a custom menu button
from my personal.xls. Just select the area desired and run it.

Sub fixmynums()
Application.ScreenUpdating = False
'lr = Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
For Each C In Selection 'Range("a1:q" & lr)
If Trim(Len(C)) > 0 And C.HasFormula = False Then
C.NumberFormat = "General"
C.Value = CDbl(C)
End If
Next

Application.ScreenUpdating = True
End Sub

 

[classic600]

Thanks so much Shane. I had problem 1. I am using Excel 2007 and since
everything has been moved around I couldn't figure out where anything was
but, thanks to your post, I got it sorted out.