To Leave Cell blank Please

[Steved]

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.

 

[Biff]

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

 

[Steved]

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.

 

[Domenic]

You can also custom format your cell...

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

Hope this helps!

 

[Biff]

Nice!

I don't think I've ever seen that one!

That's going into my stash.

Biff

 

[Domenic]

I like it too, but I have to keep referring to the help file to make
sure I've got it right.

 

[Steved]

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.

 

[Biff]

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