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 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 ✭✭✭✭✭✭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 KornaThank you! This works.
Answers
-
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 KornaThank you! This works.
-
Nick Korna ✭✭✭✭✭✭
No problem, happy to help!☺️
Help Article Resources
Categories
Try: =IF(OR(ISBLANK(Title@row), Title@row = \"Resource\"), 0, 3)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111329,"type":"question","name":"IF Statements and Summing Children","excerpt":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount column, then I'd like to show the difference between the Budgeted Amount and the Actual Amount. But I only want to calculate those values if the…","snippet":"Hi there - I need a hand with a formula. I'm trying to do the following: Show the difference between Budgeted Amount and Quote Amount unless there is a value in the Actual Amount…","categoryID":322,"dateInserted":"2023-10-06T16:41:19+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":159342,"lastUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3393230594,"url":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111329\/if-statements-and-summing-children","format":"Rich","lastPost":{"discussionID":111329,"commentID":398969,"name":"Re: IF Statements and Summing Children","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398969#Comment_398969","dateInserted":"2023-10-06T19:01:55+00:00","insertUserID":159342,"insertUser":{"userID":159342,"name":"Beth Fantozzi 1","url":"https:\/\/community.smartsheet.com\/profile\/Beth%20Fantozzi%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T19:37:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PZPFEHHKZ5S\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T19:01:13+00:00","dateAnswered":"2023-10-06T18:49:07+00:00","acceptedAnswers":[{"commentID":398967,"body":"
@Beth Fantozzi 1<\/a> <\/p> I got you - use this:<\/p> =IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row - [Actual Amount]@row, [Budgeted Amount]@row - [Quote Amount]@row)))<\/p><\/div><\/div> It ends up looking like this:<\/p> <\/p> _____________________________________________________________________________________________<\/p> 👨🏼💻 Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/strong><\/a> | Smartsheet Aligned Gold Partner<\/strong><\/p> If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and\/or ❤️Awesome).<\/em><\/p> 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p> ▶️ Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/5U7E27I89YBF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
<\/p>
