Results 1 to 9 of 9

WHy can't I get column to Autosum?

This is a discussion on WHy can't I get column to Autosum? within the Excel Questions forums, part of the Question Forums category; a b c d e 5 Fort Belvoir Kreiger 12,000 4 Quantico Kreiger 10,000 3 Fort Myer Kreiger 7,500 1 ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Location
    Norfolk, VA
    Posts
    87

    Default WHy can't I get column to Autosum?

    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.

  2. #2
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: WHy can't I get column to Autosum?

    Is the comma in the numbers a character you typed or is it the formatting comma?
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  3. #3
    Board Regular
    Join Date
    Oct 2002
    Location
    Norfolk, VA
    Posts
    87

    Default Re: WHy can't I get column to Autosum?

    Both columns and formulas have the comma in them.

  4. #4
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: WHy can't I get column to Autosum?

    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.
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  5. #5
    Board Regular
    Join Date
    Oct 2002
    Location
    Norfolk, VA
    Posts
    87

    Default

    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.

  6. #6
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: WHy can't I get column to Autosum?

    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.
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  7. #7
    Board Regular
    Join Date
    Oct 2002
    Location
    Norfolk, VA
    Posts
    87

    Default

    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.

  8. #8
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: WHy can't I get column to Autosum?

    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

  9. #9
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: WHy can't I get column to Autosum?

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com