Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Auto sum gives answer 0 when there are cell values


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Auto sum gives answer 0 when there are cell values

It sounds like there might be a formatting issue with the cells you are trying to sum. Here are a few things you can try:
  1. Check the formatting of the cells: Make sure that the cells you are trying to sum are formatted as numbers. If they are formatted as text, Excel will not be able to sum them.
  2. Check for hidden characters: Sometimes there can be hidden characters in cells that prevent Excel from recognizing them as numbers. To check for hidden characters, select the cells you want to sum and press Ctrl + H to open the Find and Replace dialog box. In the "Find what" field, type a single space and leave the "Replace with" field blank. Click "Replace All" to remove any hidden spaces.
  3. Use the SUM function: If AutoSum is not working, you can try using the SUM function instead. To use the SUM function, select the cell where you want the sum to appear and type "=SUM(" followed by the range of cells you want to sum. For example, if you want to sum cells A1 through A5, you would type "=SUM(A1:A5)".
  4. Check for errors: Finally, make sure that there are no errors in the cells you are trying to sum. If there is an error in one of the cells, Excel will not be able to sum the range. To check for errors, select the cells you want to sum and look for any error messages in the formula bar at the top of the screen.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Auto sum gives answer 0 when there are cell values

Could be the values you see are formatted as Text

Re-format all to General

Copy an empty cell.

Select the range of values and Paste SpecialAddOKEsc.


Gord Dibben MS Excel MVP

On Thu, 14 Aug 2008 10:23:02 -0700, Coylumbridge
wrote:


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Auto sum gives answer 0 when there are cell values

I am having this problem as well. I made sure that the autocalculation was
chosen in the tools. When I enter this line it will not total the numbers -
I just get $0
=SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)

"Gord Dibben" wrote:

Could be the values you see are formatted as Text

Re-format all to General

Copy an empty cell.

Select the range of values and Paste SpecialAddOKEsc.


Gord Dibben MS Excel MVP

On Thu, 14 Aug 2008 10:23:02 -0700, Coylumbridge
wrote:



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Auto sum gives answer 0 when there are cell values

Firstly get rid of the unnecessary SUM() function.
=SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)
can be replaced by
=J127+J125+J123+J121+J111+J109+J107+J105+J103+J101 +J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J16+J 11+J9+J7

Secondly, did you try the solution which Gord suggested?

The chances are that your data cells contain text not numbers. After you've
tried Gord's solution, if you're still getting a zero result, look at
=ISNUMBER(J127) and =ISTEXT(J127), [for a cell where you think you've got a
non-zero value.]
If you've still got text you may need to look for non-printing characters in
the cell.
--
David Biddulph

"Skarabat" wrote in message
...
I am having this problem as well. I made sure that the autocalculation
was
chosen in the tools. When I enter this line it will not total the
numbers -
I just get $0
=SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)

"Gord Dibben" wrote:

Could be the values you see are formatted as Text

Re-format all to General

Copy an empty cell.

Select the range of values and Paste SpecialAddOKEsc.


Gord Dibben MS Excel MVP

On Thu, 14 Aug 2008 10:23:02 -0700, Coylumbridge
wrote:







  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Firstly get rid of the unnecessary SUM() function.

Check the cell value it might be stored as "numbers stored as Text" change that to "Convert to Numbers" by clicking the dialouge box or else you format the entire row or column in numbers and then re enter the value of each cell and then do autosumation you will get the correct answer..

hoe it will be usefull....

Ragu.....



On Thursday, August 14, 2008 1:23 PM Coylumbridg wrote:





On Thursday, August 14, 2008 2:23 PM Gord Dibben wrote:


Could be the values you see are formatted as Text

Re-format all to General

Copy an empty cell.

Select the range of values and Paste SpecialAddOKEsc.


Gord Dibben MS Excel MVP



On Wednesday, November 12, 2008 10:22 AM Skaraba wrote:


I am having this problem as well. I made sure that the autocalculation was
chosen in the tools. When I enter this line it will not total the numbers -
I just get $0
=SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)

"Gord Dibben" wrote:



On Wednesday, November 12, 2008 11:13 AM David Biddulph wrote:


Firstly get rid of the unnecessary SUM() function.
=SUM(J127+J125+J123+J121+J111+J109+J107+J105+J103+ J101+J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J 16+J11+J9+J7)
can be replaced by
=J127+J125+J123+J121+J111+J109+J107+J105+J103+J101 +J99+J97+J91+J89+J64+J58+J26+J24+J22+J20+J18+J16+J 11+J9+J7

Secondly, did you try the solution which Gord suggested?

The chances are that your data cells contain text not numbers. After you've
tried Gord's solution, if you're still getting a zero result, look at
=ISNUMBER(J127) and =ISTEXT(J127), [for a cell where you think you've got a
non-zero value.]
If you've still got text you may need to look for non-printing characters in
the cell.
--
David Biddulph

"Skarabat" wrote in message
...




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 25
Default Firstly get rid of the unnecessary SUM() function.

Ragu,

Is it really necessary to respond to a post that is nearly three years
old?

Pete

On Oct 1, 10:47*am, Ragu Binu wrote:
Check the cell value it might be stored as "numbers stored as Text" change that to "Convert to Numbers" by clicking the dialouge box or else you format the entire row or column in numbers and then re enter the value of each cell and then do autosumation you will get the correct answer..

hoe it will be usefull....

Ragu.....

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto copy single values to another cell DDavid Excel Discussion (Misc queries) 5 September 27th 07 02:11 PM
Is there a way to link Auto Filter values to a Cell SteveC Excel Worksheet Functions 2 May 1st 06 07:47 PM
AUTO COMPLETE FOR CELL VALUES MH Excel Discussion (Misc queries) 0 February 16th 06 07:03 PM
Auto-increment cell values by one [email protected] Excel Worksheet Functions 2 November 30th 05 04:58 PM
How do I connect 2 Excel Sheets to give auto answer on the 3rd Maca Excel Worksheet Functions 1 November 1st 05 01:31 PM


All times are GMT +1. The time now is 08:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"