MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cannot get data to do a sum total

Posted by A.R. on October 23, 2001 8:43 AM

I received an Excel file where I need to find the sum total for a column. But the cell format is displayed as “text” so I change it to the number format. When I do an AutoSum nothing happens. I even tried to copy the data to a new worksheet and do a Paste Special and again I get no total. How do I get the data to show the sum total?

Posted by Aladin Akyurek on October 23, 2001 8:49 AM



By the way, copy an empty unformatted cell, select the range of interest, activate Edit|Paste Special, and activate Add. After all this, you should be able to use SUM.


Posted by Mark W. on October 23, 2001 9:56 AM

How to convert to numeric data type...

A.R., the act of changing the formatting of your
column from "Text" to a valid number format doesn't
cause a change of data type. You can use Excel's
Text to Columns... command to cause this change
after you changed the cell formatting of your
values to a valid number format. Just choose the
Data | Text to Columns... menu command and press
[ Finish ] at Step 1 of 1.

Posted by Aladin Akyurek on October 23, 2001 11:12 AM

A query


You started another thread about the same problem. I suspect you haven't been able to apply what I proposed.

Here another proposal:

Lets say that the numbers you want to sum in A1:A1000 in Sheet2.
Insert another sheet in this problematic file/workbook.

Type in A1 of this new sheet:


The result must be 3 of course. If this works, then in A2 in this sheet enter:


Please report what you get.


PS. If in doubt, I'd like to have a look at your file if possible.