Edit formula to return a blank result if the result is zero

Can you tell me how to edit this formula to return a blank cell instead of a zero? It is putting zeros on our dashboard graphs/charts that I want removed. thanks!!


=COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1")

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Paigebrim You can convert this to an IF that evaluates the answer and then either runs the formula or sets the column to blank:

    =IF(COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1") = 0, "", COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1"))

    In English: If this formula result equals 0, set the field to blank (that's the double quotes ""); otherwise, set the value to the result of this formula.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Try something like this:

    =IF(value = 0, REPLACE(TargetRow, 1, 1, ""), value)

    so subbing in you code:

    =IF([COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1") = 0, REPLACE([Column2]@row, 1, 1, ""), COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1"))


    I think that should work.

  • @Freymish I just tried that, it came back #UNPARSEABLE

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Paigebrim You can convert this to an IF that evaluates the answer and then either runs the formula or sets the column to blank:

    =IF(COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1") = 0, "", COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1"))

    In English: If this formula result equals 0, set the field to blank (that's the double quotes ""); otherwise, set the value to the result of this formula.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!