To Leave Cell blank Please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello from Steved

If no Data the cell is returning a 0 value I need it in this case to be
blank please.

SUM(IF('From Charters'!$A$1:$A$900="Roskill",IF('From
Charters'!$B$1:$B$900="Period.1",'From Charters'!$D$1:$D$900,0),0))

Thankyou.
 
Hi!

Sometimes it's more efficient to use conditional formatting to "hide"
unwanted returns!

Instead of using an array SUM(IF, use this:

=IF(SUMPRODUCT(--('From Charters'!$A$1:$A$900="Roskill"),--('From
Charters'!$B$1:$B$900="Period.1"),'From
Charters'!$D$1:$D$900)=0,"",SUMPRODUCT(--('From
Charters'!$A$1:$A$900="Roskill"),--('From
Charters'!$B$1:$B$900="Period.1"),'From Charters'!$D$1:$D$900))

If your wb is large with lots of these types of formulas consider this:

This formula takes twice as long to calculate and uses relatively large
arrays.

Biff
 
Hello Biff from Steved

Thankyou and yes I've taken on board your comment about large Worksheet
but in this case it is a small one

Thankyou.
 
You can also custom format your cell...

Format > Cells > Number > Custom > Type: 0;-0;;@

Hope this helps!
 
I like it too, but I have to keep referring to the help file to make
sure I've got it right. :)
 
Hello Domenic from Steved

Domenic thankyou, Thius is a much better solution because you have a much
clearner formula.

Great Stuff

Thanks again for your thoughtfulness.
 
Just be aware that just because you don't see a zero in that cell there is
one!

So, if say, you wanted to then average values in a range that carried that
format, the unseen zeros could cause a problem!

Biff
 
Back
Top