IF Multiple Conditions

Options
Mareme
Mareme
edited 12/09/19 in Smartsheet Basics

Hi

 

I m having difficulties in implementing a multiple conditions formula

 

Context:

2 key columns:

- Column status with the following available list : COMPLETED / ONGOING / NOT STARTED/LATE

- Column Completion: 

 

If have created the following formula below to automatically update the Status Column of the Parent line.

=IF([Global Completion]319 = 100, "Completed", IF([Global Completion]319 = 0, "Not started", "Ongoing"))

 

However, i also need to introduce the condition that is one of the children is LATE than the parent is LATE

 

Have tried several formula with IF but doesnt seems to work. Can someone help please?

 

Regards

 

 

 

 

Comments

  • Preston
    Preston ✭✭✭✭✭
    Options

    Mareme,

    It sounds like you could use the COUNTIF and the CHILDREN functions. Take a look at my formula at the bottom as an example. Also, I HIGHLY recommend using @row where ever possible as it helps reduce the potential for errors. I've revised your formula to show what I mean here:

    =IF([Global Completion]@row = 100, "Completed", IF([Global Completion]@row = 0, "Not started", "Ongoing"))

     

     

    =IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Update") >= 1, "Update", IF(COUNTIF(CHILDREN(), "") >= 1, "Pending CO", IF(COUNTIF(CHILDREN(), "Gray") = COUNT(CHILDREN()), "Gray", "TBD"))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    =IF(COUNTIFS(CHILDREN(), "Late") > 0, "Late", IF([Global Completion]319 = 100, "Completed", IF([Global Completion]319 = 0, "Not started", "Ongoing")))

     

    Give that a try.

  • Mareme
    Options

    Hi Preston, Paul,

    Really sorry for the late feedback.

    Thank you very much for your help.

    I've tried Paul's formula and it worked perfectly!

    Thanks alsp for the insight also Preston, will keep in mind the @row feature.

    Bests