formula not to show 0 in cells when dragging existing formula

[JEAN1972]

Am using the following formula in a range of cells column D2

Do not want the value of 0 to appear in the cells when dragging down.

Any suggestion

Please Login or Register  to view this content.

BE 0
FR 0
FR 186
IT 90
FR 0
FR 0

[AliGW]

Add an IF clause:

=IF(SUMPRODUCT(CustomerA!Y$2:Y$915,--(CustomerA!$J$2:$J$915='Report '!$A2),--(CustomerA!$K$2:$K$915='Report '!$B2),--(CustomerA!$L$2:$L$915='Report '!$C2))=0,"",SUMPRODUCT(CustomerA!Y$2:Y$915,--(CustomerA!$J$2:$J$915='Report '!$A2),--(CustomerA!$K$2:$K$915='Report '!$B2),--(CustomerA!$L$2:$L$915='Report '!$C2)))

[Pete_UK]

Rather than doubling up the formula, you can use a Custom Format to suppress the zeros so that they appear blank. If you are only expecting positive numbers, then you can use this Custom Format:

General;;;

If your numbers can be positive or negative, but you don't want to show zero, then the custom format would be:

General;-General;;

Of course, General could be replaced with a number format if you want a specific numeric display (like dates).

Another way to avoid the doubling-up of the formula is to do this:

=IFERROR(1/(1/SUMPRODUCT(CustomerA!Y$2:Y$915,--(CustomerA!$J$2:$J$915='Report '!$A2),--(CustomerA!$K$2:$K$915='Report '!$B2),--(CustomerA!$L$2:$L$915='Report '!$C2))),"")

Hope this helps.

Pete

[Pete_UK]

Another way is to leave your formula as it is, and to apply conditional formatting, such that if the cell value is zero then the foreground colour is set to white (or to whatever the background colour is for that cell), so white on white would effectively show the cell as blank.

Hope this helps.

Pete

[Glenn Kennedy]

?? An alternative:

=IFERROR(1/(1/SUMPRODUCT(CustomerA!Y$2:Y$915,--(CustomerA!$J$2:$J$915='Report '!$A2),--(CustomerA!$K$2:$K$915='Report '!$B2),--(CustomerA!$L$2:$L$915='Report '!$C2))),"")

[JEAN1972]

Thank you all for assistance work great

[Pete_UK]

You're welcome - thanks for the rep.

Pete