IF/AND/OR

Joanna Collins
Joanna Collins ✭✭✭✭
inFormulas and Functions

I am in need of a formula to look at the Finish Type column and then calculate if it is in spec based on the finish type and if the gloss is in range for that finish type. (IE. PIF gloss range 22-86, COO gloss range 36-82, TRD gloss range 44-87). If the Finish type is not in the acceptable range for that type, "Out of Spec" or "In Spec".


I have 4 available selections in Finish Type, each with their own acceptable range for gloss.

Any help would be appreciated!

image.png


· Share on Facebook Share on Twitter

Best Answer

  • Intern98
    Intern98 ✭✭✭
    edited 04/06/22 Answer ✓

    =IF(AND([FINISH TYPE]@row = "PIF", [GLOSS]@row > 23, [GLOSS]@row < 41), "In Spec",

    IF(AND([FINISH TYPE]@row = "COO", [GLOSS]@row > 8, [GLOSS]@row < 20), "In Spec",

    IF(AND([FINISH TYPE]@row = "TRD", [GLOSS]@row > 20, [GLOSS]@row < 40), "In Spec",

    IF(AND([FINISH TYPE]@row = "LAST FINISH", [GLOSS]@row > 'lower limit, [GLOSS]@row < upper), "In Spec", "Out of Spec" )))

    去年完成和各自的boundarie取代s, lower upper limit, as needed

    do let me know if it worked.

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>


<\/p>

When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":53,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"

Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106930,"type":"question","name":"Help with date formate","excerpt":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.","snippet":"I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it…","categoryID":322,"dateInserted":"2023-06-26T22:42:11+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":157924,"lastUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3375690965,"url":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106930\/help-with-date-formate","format":"Rich","lastPost":{"discussionID":106930,"commentID":382517,"name":"Re: Help with date formate","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382517#Comment_382517","dateInserted":"2023-06-27T12:53:54+00:00","insertUserID":157924,"insertUser":{"userID":157924,"name":"jpaul","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/jpaul","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/34U84XEA80NI\/nJBANQUTUTY9A.png","dateLastActive":"2023-06-27T18:03:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T12:53:24+00:00","dateAnswered":"2023-06-26T23:07:25+00:00","acceptedAnswers":[{"commentID":382460,"body":"

Assuming your result will only ever be 01-12, this should do the trick. The VALUE() function may not necessarily be needed, but I'm not sure what type of date you are pulling the month information from and it isn't going to hurt anything.<\/p>

=IF([Month Column]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(VALUE([Month Column]@row) < 7, \"S\", \"F\"))<\/p>

Replace [Month Column] with your column name.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Using SUMIFS Formula

    Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, "AMR", {leadership}, "Truttmann")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!

  • IF cell CONTAINS one/two/three different values, return corresponding values

    Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria "Consolidation", "Reduction", "Termination", "New" Currently the formula works for single values with the following formula =IF([Type of Project]@row = "Consolidation", "",…

  • Help with date formate

    I have my months extracted from a date column using =Left(EISD@row,2) I need a formula to evaluate my month column, if it's 01-06 it returns an S, if its 07-12 (or <=7) then it returns an F.