To Leave Cell blank Please
 

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

"Steved" wrote in message
...
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.

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.

"Biff" wrote:

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" wrote in message
...
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.

You can also custom format your cell...

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

Hope this helps!

In article ,
Steved wrote:

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.

Nice!

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

That's going into my stash.

Biff

"Domenic" wrote in message
...
You can also custom format your cell...

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

Hope this helps!

In article ,
Steved wrote:

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.

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

In article ,
"Biff" wrote:

Nice!

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

That's going into my stash.

Biff

"Domenic" wrote in message
...
You can also custom format your cell...

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

Hope this helps!

In article ,
Steved wrote:

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.

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.

"Domenic" wrote:

You can also custom format your cell...

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

Hope this helps!

In article ,
Steved wrote:

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.

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

"Steved" wrote in message
...
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.

"Domenic" wrote:

You can also custom format your cell...

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

Hope this helps!

In article ,
Steved wrote:

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.