Help with nested if and statements
I have an assessment with different factors to create a final score. There are four individual results that factor in. First determines critical vs noncritical. If "Critical" and any of four individual scores are "No" then 0 pts, if "Yes" then 4 pts, if "NonCritical" and 1 of the four individual scores are "No" then 2pts. If "Noncritical" and 2 or more of the four individual scores are "No" then 0 pts. if "NonCritical" 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

Hi @Lara Kelly,
I think this is what you're after:
=IF(OR(AND([Critical/NonCritical]@row = "Critical", COUNTIF([Result1]@row:[Result4]@row, "Yes") >= 1, COUNTIF([Result1]@row:[Result4]@row, "No") = 0), AND([Critical/NonCritical]@row = "NonCritical", COUNTIF([Result1]@row:[Result4]@row, "Yes") = 4)), 4, IF(AND([Critical/NonCritical]@row = "NonCritical", COUNTIF([Result1]@row:[Result4]@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. 🙂

@Nick Korna Thank you! This works.
Answers

Hi @Lara Kelly,
I think this is what you're after:
=IF(OR(AND([Critical/NonCritical]@row = "Critical", COUNTIF([Result1]@row:[Result4]@row, "Yes") >= 1, COUNTIF([Result1]@row:[Result4]@row, "No") = 0), AND([Critical/NonCritical]@row = "NonCritical", COUNTIF([Result1]@row:[Result4]@row, "Yes") = 4)), 4, IF(AND([Critical/NonCritical]@row = "NonCritical", COUNTIF([Result1]@row:[Result4]@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. 🙂

@Nick Korna Thank you! This works.

No problem, happy to help! ☺️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!