SUMPRODUCT Question...

[PokerZan]

I think a quick question here...

I have a rather lengthy SUMPRODUCT formula that I want to leave a blank cell if the product of my criteria is zero. Is there a way to do this? It seems to me that I have seen folks post here about a IFISERROR or something like that, but I thought that was to return a blank in VLOOKUP #N/A result. This is pretty similar but I can't seem to figure out how to get it done.

Thanks,

PZan

[Excel_Geek]

What if you wrap the formula in an =IF... and if it equals zero, make the cell "" (two quotes with no space in the middle).

I.e. =IF(SUMPRODUCT(...)=0,"",SUMPRODUCT(...))

[MrShorty]

Do you need the cell's value to be "" or would an appropriate number format to hide 0 values be adequate? Something like Format -> Cells -> Number -> Custom Number format -> [format code for <0];;[format code for >0] would leave all zero values appearing empty.

[Domenic]

Try custom formatting your cell...

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

Hope this helps!

In article <[email protected]>,
PokerZan <[email protected]>
wrote:

> I think a quick question here...
>
> I have a rather lengthy SUMPRODUCT formula that I want to leave a blank
> cell if the product of my criteria is zero. Is there a way to do this?
> It seems to me that I have seen folks post here about a IFISERROR or
> something like that, but I thought that was to return a blank in
> VLOOKUP #N/A result. This is pretty similar but I can't seem to figure
> out how to get it done.
>
> Thanks,
>
> PZan

[Domenic]

Note that the underlying value for the cell will be 0.

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Try custom formatting your cell...
>
> Format > Cells > Number > Custom > Type: 0;-0;;@
>
> Hope this helps!