ISBLANK or similar

[paulkaye]

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

 

[Bob Phillips]

=IF(COUNT(D2429)>0,SUM(D2429),"")


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

Try it this way:

=IF(SUM(C24:C29)=0,"",SUM(C24:C29))

Hope this helps.

Pete

 

[Don Guillett]

=SUMPRODUCT((M3:M23<>"")*(M3:M23<>" "))

 

[Bernard Liengme]

Bob's answer is more reliable since it may happen that the numbers do
actually sum to zero!

 

[paulkaye]

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?

 

[Bob Phillips]

Sorry, where is the INDIRECT?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[paulkaye]

After reading around, I understand that "" gives a zero-length string,
not a truly empty cell. I read here

http://groups.google.com/group/micr...c319f9?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

 

[Pete_UK]

Yes, I realised that, Bernard, when I saw Bob's answer - thanks for
pointing it out.

Pete

 

[paulkaye]

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)))

 

[Don Guillett]

to sum
=SUMPRODUCT((M3:M23<>"")*(M3:M23<>" "),M3:M23)

 

[paulkaye]

That gave me a zero again - I still need a way to end up with an empty
cell!

 

[Don Guillett]

Use Bob's

 

[JE McGimpsey]

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).

 

[paulkaye]

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.

 

[JE McGimpsey]

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.

 

[Bob Phillips]

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)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)