IF AND....

mragans23
mragans23 ✭
edited 10/16/23 inFormulas and Functions

I am trying to have a column show Green when the status Does not = Contacted - Need to Follow Up AND when Attempt # is less than or equal to 2 (Attempt # = 3 or 4 it should be yellow and 5 or more should be red). HELP :)

=IF(AND(Status@row <> "Contacted - Need to Follow up", "green", IF(AND([Attempt #]@row <= 2, "green", IF([Attempt #]@row = 3, "yellow", IF([Attempt #]@row = 4, "yellow", IF([Attempt #]@row > 4, "red")))))))

· Share on Facebook Share on Twitter

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    This will also do some verification to ensure the symbols are not being populated for blank rows or any non-numerical values.


    =IF(AND(Status@row <> "Contacted - Need to Follow up", Status@row <> "", ISNUMBER([Attempt #]@row)), IF([Attempt #]@row <= 2, "Green", IF([Attempt #]@row <= 4, "Yellow", IF([Attempt #]@row > 4, "Red"))))

    · Share on Facebook Share on Twitter
  • mragans23
    mragans23 ✭

    THanks, i got red and yellow but no greens.

    Formula.png


    · Share on Facebook Share on Twitter
  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    In your screenshot, none of the values in Attempt # are 2 or less. Are there rows elsewhere that are 2 or less?

    · Share on Facebook Share on Twitter
  • mragans23
    mragans23 ✭

    I appologize if I said this wrong (good possibility as I have been working on this all day).


    If status is not Contacted- Need to Follow Up, it want it to be green.

    If it is Contacted - Need to Follow Up, if it is 2 or less than green. If it is 3 or 4 then yellow and if 5 or more red. What i need to see is who we have contacted and need to follow up with and code it accordingly. If we have scheduled a meeting, then green is great.

    · Share on Facebook Share on Twitter
  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Yes, I definitely misunderstood. No worries, give this one a try:

    =IF(Status@row <> "", IF(Status@row <> "Contacted - Need to Follow up", "Green", IF(ISNUMBER([Attempt #]@row), IF([Attempt #]@row <= 2, "Green", IF([Attempt #]@row <= 4, "Yellow", "Red")))))

    · Share on Facebook Share on Twitter

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!
Hi @Hobo<\/a> ,<\/p>


<\/p>

You can use this formula. The formula will count rows where priority is high and status is not completed.<\/p>


<\/p>

=COUNTIFS(Priority:Priority, \"High\", Status:Status, <> \"Completed\")<\/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":112223,"type":"question","name":"How to pull the most recent status symbol from another sheet?","excerpt":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date\/symbol, that symbol should be automatically updated…","snippet":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th…","categoryID":322,"dateInserted":"2023-10-26T17:10:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T20:32:57+00:00","insertUserID":169134,"insertUser":{"userID":169134,"name":"Brittaney Pizzato","url":"https:\/\/community.smartsheet.com\/profile\/Brittaney%20Pizzato","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T20:42:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":133754,"lastUser":{"userID":133754,"name":"Scott Peters","url":"https:\/\/community.smartsheet.com\/profile\/Scott%20Peters","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QK9YYA22R88Y\/nEZ19DQAQ4262.png","dateLastActive":"2023-10-26T20:33:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3396693219,"url":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","format":"Rich","lastPost":{"discussionID":112223,"commentID":401926,"name":"Re: How to pull the most recent status symbol from another sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401926#Comment_401926","dateInserted":"2023-10-26T20:32:57+00:00","insertUserID":133754,"insertUser":{"userID":133754,"name":"Scott Peters","url":"https:\/\/community.smartsheet.com\/profile\/Scott%20Peters","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/QK9YYA22R88Y\/nEZ19DQAQ4262.png","dateLastActive":"2023-10-26T20:33:13+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\/F30QLRHNUSZM\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T20:42:55+00:00","dateAnswered":"2023-10-26T20:32:57+00:00","acceptedAnswers":[{"commentID":401926,"body":"

Hi @Brittaney Pizzato<\/a> - You would match the max date against the date column, and you don't need Collect in this scenario, so it could read as =INDEX({Project Health}, MATCH(MAX({Date}), {Date}, 0))<\/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":112211,"type":"question","name":"Automation Alert Row Info","excerpt":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?","snippet":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable…","categoryID":322,"dateInserted":"2023-10-26T15:56:15+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T16:07:33+00:00","insertUserID":169126,"insertUser":{"userID":169126,"name":"J.Sanabria","url":"https:\/\/community.smartsheet.com\/profile\/J.Sanabria","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:52:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":72271,"lastUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":17,"score":null,"hot":3396672828,"url":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","format":"Rich","lastPost":{"discussionID":112211,"commentID":401856,"name":"Re: Automation Alert Row Info","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401856#Comment_401856","dateInserted":"2023-10-26T16:07:33+00:00","insertUserID":72271,"insertUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05:47+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T17:52:33+00:00","dateAnswered":"2023-10-26T16:07:33+00:00","acceptedAnswers":[{"commentID":401856,"body":"

@J.Sanabria<\/a> In the notification set up, under Message includes: you would have to select Message only.<\/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

  • Countifs formula

    嗨,团队对寻求一些帮助n, i need a countifs formula to count all the high priority actions (Priority column) that are currently either not started or ongoing (Status column) Thanks in advance

  • How to pull the most recent status symbol from another sheet?

    Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date/symbol, that symbol should be automatically updated…

  • Automation Alert Row Info

    Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?