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

Paigebrim

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")

Jeff Reisman

@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.

Freymish

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.

Paigebrim

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

Jeff Reisman

@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.