Nest a function to return nothing if the result is "0"-CodePudding

If the result of the following formula is "0" how do I add to the formula to return nothing-as in the cell stays blank.

=COUNTIFS(F:F,J6)

CodePudding user response：

You have to use the IF function to check for the result.

=IF(COUNTIFS(F:F,J6)>0,COUNTIFS(F:F,J6),"")

If you have Excel 365 you can use a LET-function to avoid counting twice:

=LET(result,COUNTIFS(F:F,J6),
IF(result>0,result,""))

Or you use a special numberformat (with your original formula): 0;; which will not show negative numbers and 0

CodePudding user response：

Alternatively you can try-

=TEXT(COUNTIFS(F:F,J6),"0;;;")