Help with nested if and statements

Lara Kelly

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.

Nick Korna

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

@Nick Korna Thank you! This works.

Nick Korna

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

@Nick Korna Thank you! This works.

Nick Korna

No problem, happy to help! ☺️