Formula

I have a series deliverables reference from another sheet (sheet 1). In this sheet (sheet 2) I list those deliverables under an objective and I want to rate the entire objective based on the highest risk in my Symbols (Red being the highest risk, then Yellow, Green, Gray) what would be the formula.

Same thing for the objective checkbox, if one check box is selected the objective is a checkbox.

Find more posts tagged with

Formulas

Accepted answers

AravindGP

Hi @Eddie Endurium LLC

If I understand you correctly, you want Objective 1 (the parent row) to show Red if even one of its deliverables (children) is Red. If none are Red, then look for Yellow and make it Yellow if you find one, and so on.

You can add this formula to your parent row for the Symbol column in Sheet 2

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray")))

For Objective column (which is a checkbox), you can use this formula in Sheet 2

=IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

Eddie Endurium LLC

Thank you Aravind! Much appreciated

All comments

AravindGP

Hi @Eddie Endurium LLC

You can add this formula to your parent row for the Symbol column in Sheet 2

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray")))

For Objective column (which is a checkbox), you can use this formula in Sheet 2

=IF(COUNTIF(CHILDREN(), 1)>0, 1, 0)

Eddie Endurium LLC

Thank you Aravind! Much appreciated

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.