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
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!