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

Find more posts tagged with

Accepted answers

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.

All comments

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:

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.