Auto sum returns a 0 value in place of sum of values in column.

K

Kevin B

Have the number in the range been entered as text, or converted to text, or
imported as text?

If you're working in Excel 2002 and up you can use the smart tag to convert
the text values to numeric ones, if not you can do the following (after
making a backup copy of the workbook):

Insert a helper column to the right of your numbers
In the first cell enter the formula =VALUE(A1), substituting your first cell
address for A1
Copy down the helpler column to the last value that needs to be converted
Select the all the formulas in the helper column and press Ctrl + C to copy
then
Select the first cell in the text value column and click EDIT in the menu
Select PASTE SPECIAL and click the VALUES check box
Delete the helper column
 
G

Gord Dibben

A few less steps............

Format all to General.

Copy an empty cell.

Select the range of bogus numbers and Edit>Paste Special>Add>OK>Esc.


Gord Dibben MS Excel MVP
 
J

Jo Anna

The data was entered as numbers. My document has 7 columns each formatted
the same; however when I use the auto sum 2 columns show a 0 value. I have
tried both copying the formula and typing the formula with the same result. I
can get a calculation using multiple plus signs & entering each row/cell so
the number formatting doesn’t in error.
 
D

Dave Peterson

Formatting the cells as a number isn't enough to change the value in each of the
cells.

You have a formula like =sum(a1:a10) right?

Try this formula in an empty cell: =count(a1:a10)

This will return the number of cells that are actually numbers. Then try the
technique that Gord suggested. Watch what happens to your =sum() formula and
the =count() formula.
 
Top