Replacing false value with certain symbol or " "

Hi! I'm currently working on this project where I will roll-up my overall status using symbols. But I want the parent cell to appear either Gray or just blank if majority of the child cells are blank. Since the child cells are from another sheet where I have Not Started as blank.

Tried this formula but it still appear as Green even when all child are blank.

=IFERROR(IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN()), "Yellow"))), "Gray")

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hey @Farhana

Currently all the cells below are blank, which means the count will be 0. Since there are 0 green status symbols, then that means the two counts = the same thing, which is why you're seeing Green.

Try adding this statement in the front:

IF(COUNT(CHILDREN()) = 0, "",

=IFERROR(IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN()), "Yellow")))), "Gray")

Keep in mind that if you have a mixture of colours this will then show you a Gray symbol since none of them will equal the total.

It will also show you a Yellow, Red, or Green if only one or two are filled in, but they're the same colour. Is this what you would like? If not, you would want to compare the count of colours to the count of text in the column next to it:

=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN([Primary Column]@row)), "Green", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN([Primary Column]@row)), "Red", IF(COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN([Primary Column]@row)), "Yellow", "Gray"))))

Cheers,

Genevieve

Farhana

Yes yes that works! Except that I'm removing the Gray part. But thank you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.