Sheet with Single Row needs formula across the row?

Options

I have a sheet of indicators that pulls info from another sheet to process the correct indicator. I now need to set an overall status in the same row from the other indicators

Here is the sheet and it will always look like this just one row

So the second column Program needs to have a formula that looks at all the other items in the row to set the indicator,

If any of them are red the program status should be red, if any are yellow the program status should be yellow or it is green.

Since this was one row I did not thing a count function applied,

If was writing code I would say

If Schedule or Scope or Budget or .... is Red than Red

Else If Schedule or Scope or Budget or .... is Yellow than Yellow

Else Green.

Can I define this like this

If([Schedule] Or [Scope] Or..... = Red, Red, IF([Schedule] Or [Scope] Or..... = yellow, Yellow, Green))

Caroline

Tags:

Best Answer

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Answer ✓
    Options

    Try a nested IF formula.

    =IF(Schedule@row = "Red", "Red", IF(Scope@row = "Red", "Red", IF(Budget@row = "Red", "Red", IF(Infrastructure@row = "Red", "Red", IF(Data@row = "Red", "Red", IF(Resources@row = "Red", "Red", IF(Schedule@row = "Yellow", "Yellow", IF(Scope@row = "Yellow", "Yellow", IF(Budget@row = "Yellow", "Yellow", IF(Infrastructure@row = "Yellow", "Yellow", IF(Data@row = "Yellow", "Yellow", IF(Resources@row = "Yellow", "Yellow", "Green"))))))))))))

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Answer ✓
    Options

    Try a nested IF formula.

    =IF(Schedule@row = "Red", "Red", IF(Scope@row = "Red", "Red", IF(Budget@row = "Red", "Red", IF(Infrastructure@row = "Red", "Red", IF(Data@row = "Red", "Red", IF(Resources@row = "Red", "Red", IF(Schedule@row = "Yellow", "Yellow", IF(Scope@row = "Yellow", "Yellow", IF(Budget@row = "Yellow", "Yellow", IF(Infrastructure@row = "Yellow", "Yellow", IF(Data@row = "Yellow", "Yellow", IF(Resources@row = "Yellow", "Yellow", "Green"))))))))))))

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    There are many ways to do this, and I fully agree with @Kleerfyre!

    To throw an alternative out there... you could use a COUNTIF statement to count how many times a Red appears in the row, and if it's greater than 1 return "Red", and so on.

    For example:

    =IF(COUNTIF(Schedule@row:Resources@row, "Red") > 0, "Red", IF(COUNTIF(Schedule@row:Resources@row, "Yellow") > 0, "Yellow", "Green"))

    Cheers,

    Genevieve

  • delaurellc
    delaurellc ✭✭✭
    Options

    Johnathan,

    Perfect thank you!

    Caroline

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Options

    @Genevieve P. that's a good point too! Like your cleaner formula. Didn't think about the COUNTIF function for this.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    And just to show there are even more options:

    =IF(CONTAINS("Red", [Schedule]:[Resources]), "Red", IF(CONTAINS("Yellow", [Schedule]:[Resources]), "Yellow", "Green"))

  • delaurellc
    delaurellc ✭✭✭
    Options

    They both work, this is great!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!