Problem with Excel SUM function

J

JoeD

I have imported data from a web query into an Excel worksheet. I am trying
to add the totals of a column (SUM). My formula in cell A10 is: =SUM(A1:A9).
The result displayed in A10 is 0.

When I select cell A1 it's data is displayed in the formula bar, with an
additional space on the left side of the data. If I remove the space (delete
key), the SUM function will add just that cell. If I remove the 'space' from
in from of each entry in the column, the SUM function works fine.

I've tried the tools, options,calculation, automatic settings, but that
does not help. I've tried to "indent" or shift the entire column over, but
neither worked.

Anyone have a suggestion?

Thanks for your time.
 
M

Morrigan

Try this:

B1 = MID(A1,2,10) This will take up to 10 digit number, but in
text format
C1 = VALUE(B1) This will convert the text back into number

Now you can either SUM(C1:C9) or just copy and paste-special-value to
overwrite column A.


Hope it helps.
 
R

Ron Coderre

1)Edit one of the cells [F2]
2)Select the one blank character
3)Copy that character [Ctrl]+C
4)Select the range of "numbers"
5)Edit>Replace
-Replace what: Paste the character [Ctrl]+V
-Replace with: delete anything that might be there
-Click [Replace All]

That usually works for me.
 
D

daiblackburn

Try Highlighting the column and selecting Data > Text to columns then
hitting the finish button in the pop up box
 
B

Bernie Deitrick

JoeD,

Try:

=SUMPRODUCT(VALUE(TRIM(A1:A9)))

That may or may not work depending on what the actual lead ASCII character
is.

Alternatively, you could use

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)))

which should work no matter what the lead character is.

HTH,
Bernie
MS Excel MVP
 
J

JoeD

Bernie,

Thanks for the suggestion -- I used the

=SUMPRODUCT(VALUE(RIGHT(A1:A9,LEN(A1:A9)-1))) formula and it worked fine
until it hit a blank cell. I then received the #VALUE# error. Any
suggestions on how to get past blank cells? The range of data is around 155
rows within the column with several blank cells intermixed within the column.

Thanks in advance!

Joe
 
B

Bernie Deitrick

Joe,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(A1:A9<>"",VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)),0))

Of course, change all 3 of the A1:A9 references to your actual range.

HTH,
Bernie
MS Excel MVP
 
J

JoeD

Bernie,

THANKYOU!! Works Great!

JoeD

Bernie Deitrick said:
Joe,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(A1:A9<>"",VALUE(RIGHT(A1:A9,LEN(A1:A9)-1)),0))

Of course, change all 3 of the A1:A9 references to your actual range.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top