Unable to sum cells in worksheet

[b303lmr]

Hi everyone,

 

I am creating a manually filled schedule of sanitaryware and want to add together a column of cells. I have inputted =SUM(F2..F52) into the cell but it isn't using all of the cells in the range when calculating. 

Does anyone have any experience with this happening?

 

Thanks for your help

[Pat Stanford]

The most likely reason is that some of the values have gotten entered as text instead of as numbers.

 

Post a file with the worksheet and we can take a look. Or you should be able to trouble shoot it yourself.

 

Either add a column and a formula of the cell the that row +1. If you can't add one, then the cell is certainly text.

 

Or range your Sum range to something smaller and step it up as you find the values that have to be corrected.

 

Or check the Formatting of the cells and make sure they are not formatted as text.

 

Text will normally be left justified in a cell and number right justified, but you can use the format commands to overwrite that.

[b303lmr]

Hi Pat,

 

Thanks for getting back to me. 

I have tried re-formatting all the cells as decimal numbers, but the sum still isn't working. 

I also tried adding a column to find the culprit cells and couldn't get any of the simple addition formulas to work.

 

I've attached the file, and would appreciate if you could cast your eye over it.

 

Thanks

Sanitaryware troubleshoot.vwx

[Pat Stanford]

Some of the cells almost certainly have numbers that are stored as text.  And they don't like to be fixed. Just typing the same number over them does not work. I had to change them to something completely different, usually 2 and then back to the number they are supposed to be.

 

I added a column to the right and entered a formula of =F2+1.  I copied that cell, selected the cells in column F down to the bottom of the list and Pasted. This put the formula, with the correct reference, into each cell.  The cells causing the problem display as #VALUE.

 

Hope this helps.

 

[b303lmr]

Great thank you so much Pat, fingers crossed I can get it to behave!