IF Formulas for Status Columns

Hello,

I'm looking for assistance in writing an IF formula to change a cell's value based on the value of other cells in the same column. Specifically, the values will be symbols. If all symbols are green, I'd like the first row to show as green, if any are yellow, then the top row should be yellow, and lastly if any are red the top row should be red.

Would the best way to achieve these results be to write an IF formula?

Thanks in advanced!

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

In my example, I've assumed that the top row is a parent.

Try something like this.

=IF(COUNTIF(CHILDREN(); "Green") = COUNT(CHILDREN()); "Green"; IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; IF(COUNTIF(CHILDREN(); "Yellow") > 0; "Yellow")))

The same version but with the below changes for your and others convenience.

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

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it help/work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Making the same assumption as Andree, here is a second solution...

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", "Green"))

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.