Cannot get the column to total with =sum
in [Functions]
|
Prev: Conditionally format a cell using a list of data defined in a rang
Next: edit the cell contents command in Excel 2007 macro
From: kmg on 4 Mar 2010 14:53 oops it does NOT work. "kmg" wrote: > Just tried that and it does now work. > > "Fred Smith" wrote: > > > 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" <kmg(a)discussions.microsoft.com> wrote in message > > news:755EF622-D1D5-4381-AC1B-1CF4983F3D6A(a)microsoft.com... > > >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. > > > > . > >
From: Dave Peterson on 4 Mar 2010 15:22 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.) kmg wrote: > > 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. -- Dave Peterson
From: pmartglass on 4 Mar 2010 15:36
by any chance is manual calculation turned on ? "kmg" wrote: > 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. |