Sum(Start Cell:End Cell) Not Working

Occasional Contributor

Hey all -


Just trying to sum some data in a spreadsheet I imported. Figure something must be set or wrong with the data, but cannot figure out what...? Any help appreciated.


I have a column of numbers I'm trying to sum. This whole column is behaving strangely. For one, I cannot convert its formatting to currency. I change the format. It shows the format as being currency. is not displayed as...currency. It merely is displayed as a general number. The fix is to go to each cell and change the number in the cell, such as by adding 1. Once that is done...I can change the formatting. ?!? Have never seen this before.


Note that although I cannot use the sum function, I can add them using this approach: "=F13+F14+..." Or, I can manually go into each cell and change the number, then change it back. Then, the sum() works for the numbers that have been "unlocked" in this fashion. 


Can anyone explain what is going on or how to fix it in bulk? I don't want to tweak each number in the column, as there are hundreds of them. One other clue, is that the imported spreadsheet has protection turned on to some fashion. However, for the affected cells, I can change their values (and still should be able to use those values in the sum(). If I don't go in and manually tweak each value, the sum() does not show an error but just shows the sum of that range as 0.




P.S. In image below, the number 545,041 is already in the currency format...yet it is not showing as currency. The numbers above it have been manually manipulated by changing the value, then changing it back to the original number--which allowed me to change the format. I have tried restarting Excel but it did not fix the issue; have also tried copying numbers to another place w/o formatting, etc. but same problem persists.



2 Replies
best response confirmed by Sergei Baklan (MVP)


The cells are seen as text.

Select them, then click Text to Columns on the Data tab of the ribbon.

Without changing the settings, click Finish.

Yay! Thank you! I had presumed that changing the format would work on text, such as changing "0" text to long as was a number, it would work, was the theory. Appreciated!