Cannot get the column to total with =sum

[kmg]

I am working on a lengthy worksheet and have many link to one colmn from
other worksheets so that I can get the totals of all in the column. When I
put in the simple formula of =sum(B10:B2) the total comes up as 0. I tried
setting the column to general, does not work, tried the copy a blank to the
column with paste special an add, does not work. Do not know what next to
trouble shoot with.

 

[Bernard Liengme]

Very hard to advise without seeing the data
Want to send me a file -get my email from my website
best wishes

 

[Fred Smith]

All the problems you've listed makes it look like your data is text, not
numbers.

What happens when you simple enter a number like 10 in, say, B3? Does your
sum change to 10? If so, it means your range contains text, not numbers.
That's what you have to fix.

Regards,
Fred

 

[kmg]

I have sent you a sample of the file.

Very hard to advise without seeing the data
Want to send me a file -get my email from my website
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



.

 

[kmg]

Just tried that and it does now work.

All the problems you've listed makes it look like your data is text, not
numbers.

What happens when you simple enter a number like 10 in, say, B3? Does your
sum change to 10? If so, it means your range contains text, not numbers.
That's what you have to fix.

Regards,
Fred



.

 

[kmg]

oops it does NOT work.

Just tried that and it does now work.

 

[Dave Peterson]

First, I'm not sure what range you really used.

=sum(b10:b2)
gets changed by excel to:
=sum(b2:b10)

But try this (change the range if you need to):

=counta(b2:b10)
and
=count(b2:b10)

=counta() counts the number of non-empty cells (text or numbers) in that range.
=count() counts the number of numbers in that range.

If you think you have real numbers in that range (and only real numbers), then
these formulas should match and be equal to 9.

If the =count() returns 0, then my bet is that you copied the data from a web
page and you got some of those HTML non-breaking spaces in your data. They look
ok, but the values are really text.

Saved from a previous post.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[pmartglass]

by any chance is manual calculation turned on ?