Best way to show blank cell if value if zero

Question

=COUNTIFS(Orders!$T:$T,$B4)

is a code that gives 0 or a +ve result

I use this across 1500 cells which makes the sheet gets filled with 0s

I'd like to remove the Zeros by using the following formula

if(COUNTIFS(Orders!$T:$T,$B3,Orders!$F:$F,""&P$1&"*")=0,
"",
COUNTIFS(Orders!$T:$T,$B3,Orders!$F:$F,""&P$1&"*"))

This calculates every formula twice and increases the calculation time.

How can we do this in 1 formula where if the value is 0 - keep empty - otherwise display the answer

Answer 1

I suggest this cell-function:

=IFERROR(1/(1/COUNTIFS(Orders!$T:$T,$B4)))

EDIT: I'm not sure what to add as explanation. Basically to replace the result of a complex calculation with blank cells if it results in 0, you can wrap the complex function in

IFERROR(1/(1/  ComplexFunction()   ))

It works by twice taking the inverse (1/X) of the result, thus returning the original result in all cases except 0 where a DIV0 error is generated. This error is then caught by IFERROR to result in a blank cell.

The advantage of this method is that it doesn't need to calculate the complex function twice, so can give a significant speed/readability increase, and doesn't fool the output like a custom number format which can be important if this cell is used in further functions.

Answer 2

You only need to set the number format for your range of cells.

Go to the menu Format-->Number-->More Formats-->Custom Number Format...

In the entry area at the top, enter the following: #;-#;""

The "format" of the format string is

(positive value format) ; (negative value format) ; (zero value format)

You can apply colors or commas or anything else. See this link for details

Answer 3

instead of your =COUNTIFS(Orders!$T:$T,$B4) use:

=REGEXREPLACE(""&COUNTIFS(Orders!$T:$T,$B4), "^0$", )

also, to speed up things you should avoid "per row formulae" and use ArrayFormulas