# ISBLANK or similar

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

B

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

P

#### Pete_UK

Try it this way:

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

Hope this helps.

Pete

D

#### Don Guillett

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

B

#### Bernard Liengme

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

P

#### 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?

B

#### Bob Phillips

Sorry, where is the INDIRECT?

--
HTH

Bob

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

P

#### Pete_UK

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

Pete

P

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

D

#### Don Guillett

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

P

#### paulkaye

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

D

Use Bob's

J

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

P

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

J

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

B

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