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
-
@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
-
@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
Categories
Check out the Formula Handbook template!