 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Empty cells
I have a column of cells, some which have data (an amount of cash such as £2.50) and some which have the symbol #NA because no data has yet been entered. What I would like to do is add up the column showing the total cash so far accrued! but I cannot get the autosum function to work whilst some of the cells are still showing the #NA!! Is there a way round this please, should I be using something other than autosum? Try =SUMIF(A1:A6,">0") If you have both positive and negative numbers use: =SUMIF(A1:A6,">0")+SUMIF(A1:A6,"<0") BY the way SUM is called the SUM function; 'autosum' is the tool one can use to enter the SUM (or other functions like AVERAGE) into a cell. best wishes  SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I have a column of cells, some which have data (an amount of cash such as >£2.50) and some which have the symbol #NA because no data has yet been [quoted text clipped - 6 lines] > Is there a way round this please, should I be using something other than > autosum? Harry, This might work for you: =SUM(IF(ISERROR(A2:A5),0,A2:A5)) It's an array formula -- commit with Ctrl-Shift-Enter, not just Enter. SignatureEarl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- >I have a column of cells, some which have data (an amount of cash such as £2.50) and some >which have the symbol #NA because no data has yet been entered. [quoted text clipped - 4 lines] > > Is there a way round this please, should I be using something other than autosum? Bernard's solution worked just fine for me so I did not try any others, thanks for the help! >I have a column of cells, some which have data (an amount of cash such as >£2.50) and some which have the symbol #NA because no data has yet been [quoted text clipped - 6 lines] > Is there a way round this please, should I be using something other than > autosum?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.