Help with nested if and statements

Lara Kelly
Lara Kelly ✭✭✭
edited 10/05/23 in Formulas and Functions

I have an assessment with different factors to create a final score. There are four individual results that factor in. First determines critical vs non-critical. If "Critical" and any of four individual scores are "No" then 0 pts, if "Yes" then 4 pts, if "Non-Critical" and 1 of the four individual scores are "No" then 2pts. If "Non-critical" and 2 or more of the four individual scores are "No" then 0 pts. if "Non-Critical" and all are "Yes" then 4pts. I am having trouble with the if 1 of 4 and 2 or more parts of the formula.


Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @Lara Kelly,

    I think this is what you're after:

    =IF(OR(AND([Critical/Non-Critical]@row = "Critical", COUNTIF([Result-1]@row:[Result-4]@row, "Yes") >= 1, COUNTIF([Result-1]@row:[Result-4]@row, "No") = 0), AND([Critical/Non-Critical]@row = "Non-Critical", COUNTIF([Result-1]@row:[Result-4]@row, "Yes") = 4)), 4, IF(AND([Critical/Non-Critical]@row = "Non-Critical", COUNTIF([Result-1]@row:[Result-4]@row, "No") = 1), 2, 0))

    Sample output:

    The position of a Yes/No/blank won't matter due to using COUNTIF.

    If I've misunderstood one of the criteria or you've any problems/questions then please advise. πŸ™‚

  • Lara Kelly
    Lara Kelly ✭✭✭
    Answer βœ“

    @Nick Korna Thank you! This works.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“

    Hi @Lara Kelly,

    I think this is what you're after:

    =IF(OR(AND([Critical/Non-Critical]@row = "Critical", COUNTIF([Result-1]@row:[Result-4]@row, "Yes") >= 1, COUNTIF([Result-1]@row:[Result-4]@row, "No") = 0), AND([Critical/Non-Critical]@row = "Non-Critical", COUNTIF([Result-1]@row:[Result-4]@row, "Yes") = 4)), 4, IF(AND([Critical/Non-Critical]@row = "Non-Critical", COUNTIF([Result-1]@row:[Result-4]@row, "No") = 1), 2, 0))

    Sample output:

    The position of a Yes/No/blank won't matter due to using COUNTIF.

    If I've misunderstood one of the criteria or you've any problems/questions then please advise. πŸ™‚

  • Lara Kelly
    Lara Kelly ✭✭✭
    Answer βœ“

    @Nick Korna Thank you! This works.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, happy to help! ☺️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!