Excel 2007 : Autosum doesn't work

**LearnerEXL** · 09-24-2009, 01:26 AM

Dear Friends,

I copied data from html file and pasted in MS Excel 2007. Now, when I try to calculate the values using Auto Sum. It just shows "0". How to solve the problem? Other Excel Sheets which I am manually typing are working fine.

Thanks in advance.

**DonkeyOte** · 09-24-2009, 01:32 AM

If copied from the web I suspect the values contain a non-breaking space - this is CHAR(160)

To correct the simplest approach is to:

a) highlight the numerical data

b) run Edit -> Replace
-- Find What: hold ALT and type 0160 on Numerical keypad if using a laptop hold ALT and FN and type 0160 on numerical keypad.
-- Replace With: leave blank
-- click OK.

You should find your numerical values are coerced to numbers and your SUM now returns a value.

The other alternative which works without altering the underlying values is to use SUMPRODUCT rather than SUM, ie:

=SUMPRODUCT(--SUBSTITUTE(A1:A10,CHAR(160),""))

but this is less efficient than correcting the underlying data first hand

**LearnerEXL** · 09-24-2009, 05:55 AM

Originally Posted by DonkeyOte

If copied from the web I suspect the values contain a non-breaking space - this is CHAR(160)

To correct the simplest approach is to:

a) highlight the numerical data

b) run Edit -> Replace
-- Find What: hold ALT and type 0160 on Numerical keypad if using a laptop hold ALT and FN and type 0160 on numerical keypad.
-- Replace With: leave blank
-- click OK.

You should find your numerical values are coerced to numbers and your SUM now returns a value.

The other alternative which works without altering the underlying values is to use SUMPRODUCT rather than SUM, ie:

=SUMPRODUCT(--SUBSTITUTE(A1:A10,CHAR(160),""))

but this is less efficient than correcting the underlying data first hand

Thanks for your reply. I didn't get it clearly. I am attching the Excel file where I copied the data froma a webpage (html). Can you please demonstrate it on this attached sheet?

Thanks again.