ISBLANK or similar

  • Thread starter Thread starter paulkaye
  • Start date Start date
P

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
 
=IF(COUNT(D24:D29)>0,SUM(D24:D29),"")


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob's answer is more reliable since it may happen that the numbers do
actually sum to zero!
 
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?
 
Sorry, where is the INDIRECT?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Yes, I realised that, Bernard, when I saw Bob's answer - thanks for
pointing it out.

Pete
 
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)))
 
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).
 
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.
 
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.
 
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)
 
Back
Top