 |
 | 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 / December 2007ISBLANK or similar
Hi, I'm trying to get this SUM calculation: =SUM(C24:C29) to display an answer only if one or more of the cells in the range C24:C29 contain a value. I have tried this: =IF(ISBLANK(SUM(C24:C29)),"",SUM(C24:C29)) and it doesn't seem to have made any difference - I get 0 rather than a blank cell. Is there a way to do what I'm trying to do? Thanks! Paul =IF(COUNT(D24:D29)>0,SUM(D24:D29),"")  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, > [quoted text clipped - 13 lines] > > Paul That seemed to work at first but has led to another issue. I have an INDIRECT formula that refers to this cell. Although the cell appears to be blank, I'm getting a #VALUE! error in the cell containing the INDIRECT formula. Is there perhaps another version of 'empty' to try? > =IF(COUNT(D24:D29)>0,SUM(D24:D29),"") > [quoted text clipped - 23 lines] > > > Paul Sorry, where is the INDIRECT? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > That seemed to work at first but has led to another issue. I have an > INDIRECT formula that refers to this cell. Although the cell appears [quoted text clipped - 29 lines] >> >> > Paul The INDIRECT is somewhere else completely. It's a long story but, basically, another cell refers to this cell and I believe that the cell's non-blank status is causing the problem (see last message). If it does help, here is the exact formula that is resulting in the problem (C23 is the cell that contains the original formula that we are discussing). =IF(ISBLANK(INDIRECT("'" & C$2 & "'!"&ADDRESS(ROW($C23),COLUMN($C23), 4))),"",0-INDIRECT("'" & C$2 & "'!"&ADDRESS(ROW($C23),COLUMN($C23), 4))) > Sorry, where is the INDIRECT? > [quoted text clipped - 42 lines] > > >> > Paul Paul, Change it to =IF(LEN(INDIRECT("'" & C$2 & "'!"&ADDRESS(ROW($C23),COLUMN($C23),4)))=0,"", 0-INDIRECT("'" & C$2 & "'!"&ADDRESS(ROW($C23),COLUMN($C23),4))) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > The INDIRECT is somewhere else completely. It's a long story but, > basically, another cell refers to this cell and I believe that the [quoted text clipped - 55 lines] >> >> >> > Paul After reading around, I understand that "" gives a zero-length string, not a truly empty cell. I read here http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/364ef 6a33e0dcf9d/d42933d61dc319f9?lnk=gst&q=empty+macro#d42933d61dc319f9 that the macro cellref.Value = Empty should do the trick but I have no idea how to do this! Can someone explain? Thanks! Paul > That seemed to work at first but has led to another issue. I have an > INDIRECT formula that refers to this cell. Although the cell appears [quoted text clipped - 32 lines] > > > > Paul Try it this way: =IF(SUM(C24:C29)=0,"",SUM(C24:C29)) Hope this helps. Pete > Hi, > [quoted text clipped - 13 lines] > > Paul Bob's answer is more reliable since it may happen that the numbers do actually sum to zero! SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > Try it this way: > [quoted text clipped - 21 lines] >> >> Paul Yes, I realised that, Bernard, when I saw Bob's answer - thanks for pointing it out. Pete On Dec 10, 2:14 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca> wrote: > Bob's answer is more reliable since it may happen that the numbers do > actually sum to zero! [quoted text clipped - 30 lines] > > - Show quoted text - =SUMPRODUCT((M3:M23<>"")*(M3:M23<>" ")) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi, > [quoted text clipped - 13 lines] > > Paul to sum =SUMPRODUCT((M3:M23<>"")*(M3:M23<>" "),M3:M23) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > =SUMPRODUCT((M3:M23<>"")*(M3:M23<>" ")) > [quoted text clipped - 15 lines] >> >> Paul That gave me a zero again - I still need a way to end up with an empty cell! > to sum > =SUMPRODUCT((M3:M23<>"")*(M3:M23<>" "),M3:M23) [quoted text clipped - 28 lines] > > >> Paul Use Bob's SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > That gave me a zero again - I still need a way to end up with an empty > cell! [quoted text clipped - 35 lines] >> >> >> Paul I used Bob's formula but, as I said before, "" is giving an zero- length string which is causing me problems elsewhere. I need to modify it so I get a truly empty cell. > Use Bob's > [quoted text clipped - 42 lines] > > >> >> Paul Not possible - your cell contains a formula, and formulae *always* return a value to their calling cell. If you need the cell to appear blank, you'll need to trap the null string in follow-on calculations. That, or switch to a VBA solution. In article <a7fa4e84-dd6e-4cb6-8c18-737da6732742@w40g2000hsb.googlegroups.com>, > I used Bob's formula but, as I said before, "" is giving an zero- > length string which is causing me problems elsewhere. I need to modify > it so I get a truly empty cell. One way: =IF(COUNT(C24:C29)>0,SUM(C24:C29),"") Note, the cell won't be empty - it will have a text value (the null string). In article <a769ec92-3e81-4824-afa5-6de70ac61890@f3g2000hsg.googlegroups.com>, > That gave me a zero again - I still need a way to end up with an empty > cell! [quoted text clipped - 35 lines] > > > > >> Paul
|
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.