To Leave Cell blank Please

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
>> Steved <[email protected]> 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 <[email protected]>,
> Steved <[email protected]> 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 <[email protected]>,
"Biff" <[email protected]> wrote:

> Nice!
>
> I don't think I've ever seen that one!
>
> That's going into my stash.
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > You can also custom format your cell...
> >
> > Format > Cells > Number > Custom > Type: 0;-0;;@
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Steved <[email protected]> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> You can also custom format your cell...
>
> Format > Cells > Number > Custom > Type: 0;-0;;@
>
> Hope this helps!
>
> In article <[email protected]>,
> Steved <[email protected]> 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.

You can also custom format your cell...

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

Hope this helps!

In article <[email protected]>,
Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> You can also custom format your cell...
>
> Format > Cells > Number > Custom > Type: 0;-0;;@
>
> Hope this helps!
>
> In article <[email protected]>,
> Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
>> Steved <[email protected]> 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 <[email protected]>,
> Steved <[email protected]> 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 <[email protected]>,
"Biff" <[email protected]> wrote:

> Nice!
>
> I don't think I've ever seen that one!
>
> That's going into my stash.
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > You can also custom format your cell...
> >
> > Format > Cells > Number > Custom > Type: 0;-0;;@
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Steved <[email protected]> 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.

You can also custom format your cell...

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

Hope this helps!

In article <[email protected]>,
Steved <[email protected]> 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 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
>> Steved <[email protected]> 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 <[email protected]>,
> Steved <[email protected]> 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.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
"Biff" <[email protected]> wrote:

> Nice!
>
> I don't think I've ever seen that one!
>
> That's going into my stash.
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > You can also custom format your cell...
> >
> > Format > Cells > Number > Custom > Type: 0;-0;;@
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> You can also custom format your cell...
>
> Format > Cells > Number > Custom > Type: 0;-0;;@
>
> Hope this helps!
>
> In article <[email protected]>,
> Steved <[email protected]> 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 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> You can also custom format your cell...
>
> Format > Cells > Number > Custom > Type: 0;-0;;@
>
> Hope this helps!
>
> In article <[email protected]>,
> Steved <[email protected]> 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 <[email protected]>,
> Steved <[email protected]> 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 <[email protected]>,
"Biff" <[email protected]> wrote:

> Nice!
>
> I don't think I've ever seen that one!
>
> That's going into my stash.
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > You can also custom format your cell...
> >
> > Format > Cells > Number > Custom > Type: 0;-0;;@
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Steved <[email protected]> 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.

You can also custom format your cell...

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

Hope this helps!

In article <[email protected]>,
Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
>> Steved <[email protected]> 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.
>>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> You can also custom format your cell...
>
> Format > Cells > Number > Custom > Type: 0;-0;;@
>
> Hope this helps!
>
> In article <[email protected]>,
> Steved <[email protected]> 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 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
"Biff" <[email protected]> wrote:

> Nice!
>
> I don't think I've ever seen that one!
>
> That's going into my stash.
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > You can also custom format your cell...
> >
> > Format > Cells > Number > Custom > Type: 0;-0;;@
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Steved <[email protected]> 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.

You can also custom format your cell...

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

Hope this helps!

In article <[email protected]>,
Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
>> Steved <[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
>> Steved <[email protected]> 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 <[email protected]>,
> Steved <[email protected]> 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.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
"Biff" <[email protected]> wrote:

> Nice!
>
> I don't think I've ever seen that one!
>
> That's going into my stash.
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > You can also custom format your cell...
> >
> > Format > Cells > Number > Custom > Type: 0;-0;;@
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > Steved <[email protected]> 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.

You can also custom format your cell...

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

Hope this helps!

In article <[email protected]>,
Steved <[email protected]> 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 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> You can also custom format your cell...
>
> Format > Cells > Number > Custom > Type: 0;-0;;@
>
> Hope this helps!
>
> In article <[email protected]>,
> Steved <[email protected]> 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 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.