Problem with Excel SUM function

[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.

[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.

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.

[daiblackburn]

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

"JoeD" <[email protected]> wrote in message
news:[email protected]...
> 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.

[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.
--
Regards,
Ron

[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


"JoeD" <[email protected]> wrote in message
news:[email protected]...
> 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.

[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


"Bernie Deitrick" wrote:

> 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
>
>
> "JoeD" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

[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


"JoeD" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Bernie Deitrick" wrote:
>
>> 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
>>
>>
>> "JoeD" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

[JoeD]

Bernie,

THANKYOU!! Works Great!

JoeD

"Bernie Deitrick" wrote:

> 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
>
>
> "JoeD" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> 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
> >>
> >>
> >> "JoeD" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >>
> >>
> >>
>
>
>