WHy can't I get column to Autosum?

[sopranoiam]

a b c d e

5 Fort Belvoir Kreiger 12,000
4 Quantico Kreiger 10,000
3 Fort Myer Kreiger 7,500
1 Dahlgren Kreiger 4,000 0


Please help me figure out why I can't autosum the total of column d in the cell of column e where the 0 is.

The cells are all formatted correctly with numbers, column d contains an IF statement which runs correctly. But when I tell e5 to autosum d2:d5, the result is always 0. What am I doing wrong? I've tried to remove the formatting and re-enter it and the info and the result is always the same.

 

[Cbrine]

Is the comma in the numbers a character you typed or is it the formatting comma?

 

[sopranoiam]

Both columns and formulas have the comma in them.

 

[Cbrine]

Does Column E have calculated values? or is it data entry values. I'm trying to determine if you have typed in the commas on the numbers, in which case excel would recognize them as strings. The sum() function will not work on strings.

Just had a thought, you might want to try using the text to columns function to format the values as general. I know if you use it to change values to text, then functions still ID them as strings and won't work, even though they look like values.

 

[sopranoiam]

I got rid of all the commas in the formatting of the cells and the formula and it still doesn't work. It is really strange, even our Excel whiz here at work couldn't figure it out.

 

[Cbrine]

Did you try the text to columns thing? It sounds like that might be the problem? Otherwise I can't think of anything else off hand.

 

[sopranoiam]

Nope that didn't work, but what I did do is copy and paste =Value into a new column and try to autosum that, and it did work. Really strange why the other way wouldn't work. Thanks for the help.

 

[parry]

Hi, it wont sum the value if its treated as text so this suggests either the cells are formatted as text or there is some character included in the cell that you cant see. For unseen characters you can do a LEN formula on the cells to see if this is the case (ie if the len value is longer than the # characters you can see then theres something else in the cell).

An easy way to see whether a number is treated as text is if the value is left aligned in the cell rather than right aligned. To make sure the values are actually numbers than try this conversion technique ...

1) Put a 1 into a blank cell and copy it
2) Select all the cells in your target sum range and select Edit|Paste Special
and choose the Multiply option.

Multiplying by 1 coerces the value into a number. However this wont work if there is certain characters included in the value. Tell me if this had any effect.

hth

 

[parry]

Hi again, its quite hard to see where your data separates into columns in your post. If the word Kreiger is included in the value (ie Kreiger 12,000 instead of 12,000) then this would be another reason why it wont add as this is a text value. I presume you had checked that but maybe not.

To format data for the board here then download Colo's HTML Maker as per the link at the bottom of this page. There are several versions - the light one should do.