If statements with multiple conditions

Ireland Adgate

I have a project status column that uses the circular colored symbols . There are indented rows with symbols and I want the parent row to have a green circle if all of the indented rows have green symbols. If there is at least one yellow circle, I want the parent circle to be yellow. If there is at least one red symbol, I want the parent symbol to be red. Any help would be appreciated.

sean59916

Hi Ireland - 

 

Can you clarify what you would expect to see at the parent row if you have 3 children - one is green, yellow, red. Are you expecting a yellow or red parent icon?

 

Sean

Ireland Adgate

Yes, sorry for the confusion. Red is the most severe and would make the parent red. The parent would be yellow if any of the children are yellow AND none of them are red.

Andrée Starå

Hi Ireland,

Try something like this.

Place the formula below in the Parent of the Status column.

=IF(COUNTIF(CHILDREN(); "Green") = COUNT(CHILDREN()); "Green"; IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; "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", "Yellow"))

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

Did it work?

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting