Tags: cells, column, excel, formated, itry, microsoft, msdn, number, numbers, software, sum, unable, values

Unable to Sum() Number Values

On Microsoft » Microsoft Excel

4,837 words with 5 Comments; publish: Sat, 07 Jun 2008 23:30:00 GMT; (30677.88, « »)

Hi Everyone,

Can you help me please.

I have a column of numbers. I have formated the cells as number but when I

try to sum() the column it does not calculate, it just leaves the words

Sum() in the cell. Also any cells with zero in them display as 0 and not

0.00 it's as if the contents of the cells are still text but I have

formatted to number.

Does anyone know what's going on and how I get over this.

Thanks for your help it's appreciated.

Best Regards,

Steve Wilson.

All Comments

Leave a comment...

  • 5 Comments
    • Steve

      'Numbers' often get stuck as text in Excel. Enter a 1 in a blank cell and

      copy it. Highlight the text numbers and then Edit>Paste

      special...>Values+Multiply. This will 'shift' them into being seen by Excel

      as real numbers

      HTH

      Nick Hodge

      Microsoft MVP - Excel

      Southampton, England

      www.nickhodge.co.uk

      nick_hodgeTAKETHISOUT.excel.todaysummary.com.zen.co.uk.ANDTHIS

      "Always Learning" <NoMoreSpam.excel.todaysummary.com.MyEmail> wrote in message

      news:ur8v6FS4FHA.3976.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...

      > Hi Everyone,

      > Can you help me please.

      > I have a column of numbers. I have formated the cells as number but when I

      > try to sum() the column it does not calculate, it just leaves the words

      > Sum() in the cell. Also any cells with zero in them display as 0 and not

      > 0.00 it's as if the contents of the cells are still text but I have

      > formatted to number.

      > Does anyone know what's going on and how I get over this.

      > Thanks for your help it's appreciated.

      > Best Regards,

      > Steve Wilson.

      >

      #1; Sat, 07 Jun 2008 23:32:00 GMT
    • To prove Nick's suggestion, try this. Enter

      =SUMPRODUCT(--(A1:A10))

      and see if you get the correct results (adjust the range to suit)

      HTH

      RP

      (remove nothere from the email address if mailing direct)

      "Always Learning" <NoMoreSpam.excel.todaysummary.com.MyEmail> wrote in message

      news:ur8v6FS4FHA.3976.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...

      > Hi Everyone,

      > Can you help me please.

      > I have a column of numbers. I have formated the cells as number but when I

      > try to sum() the column it does not calculate, it just leaves the words

      > Sum() in the cell. Also any cells with zero in them display as 0 and not

      > 0.00 it's as if the contents of the cells are still text but I have

      > formatted to number.

      > Does anyone know what's going on and how I get over this.

      > Thanks for your help it's appreciated.

      > Best Regards,

      > Steve Wilson.

      >

      #2; Sat, 07 Jun 2008 23:33:00 GMT
    • Hi

      Do you see "Sum(...)" or "=Sum(...)" in cell?

      When 1st is the case, then you left '=' out from formula. Otherwise check

      format of cell with formula - it must'nt be text.

      Arvi Laanemets

      ( My real mail address: arvil<at>tarkon.ee )

      "Always Learning" <NoMoreSpam.excel.todaysummary.com.MyEmail> wrote in message

      news:ur8v6FS4FHA.3976.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...

      > Hi Everyone,

      > Can you help me please.

      > I have a column of numbers. I have formated the cells as number but when I

      > try to sum() the column it does not calculate, it just leaves the words

      > Sum() in the cell. Also any cells with zero in them display as 0 and not

      > 0.00 it's as if the contents of the cells are still text but I have

      > formatted to number.

      > Does anyone know what's going on and how I get over this.

      > Thanks for your help it's appreciated.

      > Best Regards,

      > Steve Wilson.

      >

      #3; Sat, 07 Jun 2008 23:34:00 GMT
    • Also check Tools|Options|View and make sure that Formulas is not checked.

      Jerry

      Arvi Laanemets wrote:

      > Hi

      > Do you see "Sum(...)" or "=Sum(...)" in cell?

      > When 1st is the case, then you left '=' out from formula. Otherwise check

      > format of cell with formula - it must'nt be text.

      #4; Sat, 07 Jun 2008 23:35:00 GMT
    • Hi Guys,

      Thanks for your help and time.

      Nicks tip solved it for me.

      Best Regards,

      Steve Wilson.

      "Arvi Laanemets" <garbage.excel.todaysummary.com.hot.ee> wrote in message

      news:%23Rjr$kS4FHA.400.excel.todaysummary.com.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]

      > Hi

      > Do you see "Sum(...)" or "=Sum(...)" in cell?

      > When 1st is the case, then you left '=' out from formula. Otherwise check

      > format of cell with formula - it must'nt be text.

      >

      > --

      > Arvi Laanemets

      > ( My real mail address: arvil<at>tarkon.ee )

      >

      > "Always Learning" <NoMoreSpam.excel.todaysummary.com.MyEmail> wrote in message

      > news:ur8v6FS4FHA.3976.excel.todaysummary.com.TK2MSFTNGP15.phx.gbl...

      I

      >

      #5; Sat, 07 Jun 2008 23:36:00 GMT