Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook templateto view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
❓️Product questions
Sign in andask it here!The community's got your back.

Wondering if there’s a problem?
Check out theStatus Pageand subscribe for notifications.

Share your knowledge!
Help others byanswering questions.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Discussion List

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
@Shari D<\/a> My apologies, should have been HAS<\/p>

=COUNTIFS(Status:Status, \"Open\", Severity:Severity, \"High\", [Impacted IT Projects]:[Impacted IT Projects], HAS(@cell, \"IT Project Name 1\"))<\/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":109158,"type":"question","name":"Checkbox, Texts, RYG","excerpt":"I am trying to create a formula based on checkbox and dropdown list column. The result I am looking for is for Column 3 to have either a Red or Green symbol based on if Column 1 is checked OR Column 2 includes a specific word. Column 2 is a drop down with multiple options. Is this possible?","snippet":"I am trying to create a formula based on checkbox and dropdown list column. The result I am looking for is for Column 3 to have either a Red or Green symbol based on if Column 1…","categoryID":322,"dateInserted":"2023-08-18T16:30:48+00:00","dateUpdated":"2023-08-18T18:25:24+00:00","dateLastComment":"2023-08-18T21:05:50+00:00","insertUserID":160871,"insertUser":{"userID":160871,"name":"Nana250","url":"https:\/\/community.smartsheet.com\/profile\/Nana250","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:05:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":160871,"lastUserID":160871,"lastUser":{"userID":160871,"name":"Nana250","url":"https:\/\/community.smartsheet.com\/profile\/Nana250","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:05:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":49,"score":null,"hot":3384772598,"url":"https:\/\/community.smartsheet.com\/discussion\/109158\/checkbox-texts-ryg","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109158\/checkbox-texts-ryg","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109158,"commentID":391501,"name":"Re: Checkbox, Texts, RYG","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391501#Comment_391501","dateInserted":"2023-08-18T21:05:50+00:00","insertUserID":160871,"insertUser":{"userID":160871,"name":"Nana250","url":"https:\/\/community.smartsheet.com\/profile\/Nana250","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:05:33+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-08-18T21:05:36+00:00","dateAnswered":"2023-08-18T21:02:55+00:00","acceptedAnswers":[{"commentID":391500,"body":"

@Nana250<\/a> <\/p>

Try this format and update columns names as needed: =IF(OR(CONTAINS(\"Insert Word\", [Column 2]@row), [Column 1]@row = 1), \"Green\", \"Red\")<\/p>

If column 1 is checked or it finds the word in Column 2 it will go green, if not Red. Adjust as needed.<\/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":109156,"type":"question","name":"Using Join(Collect to collect data from multiple sheets to populate in (1) cell","excerpt":"I am looking to combine data from (3) sheets into one sheet. There will be 3 columns: Team, Assignment, and Notes The cells in the Team and Assignment columns from Sheet1, Sheet2 and Sheet3 are populated from a multi-select dropdown lists. Notes are free type. Example: p-id on the destination sheet is xx, Sheet1 and Sheet2…","snippet":"I am looking to combine data from (3) sheets into one sheet. There will be 3 columns: Team, Assignment, and Notes The cells in the Team and Assignment columns from Sheet1, Sheet2…","categoryID":322,"dateInserted":"2023-08-18T16:26:31+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T20:39:19+00:00","insertUserID":164856,"insertUser":{"userID":164856,"name":"DB393","title":"Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/DB393","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!8n8md1Op_fo!!iMgn5uz8Sbv","dateLastActive":"2023-08-18T20:39:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":164856,"lastUser":{"userID":164856,"name":"DB393","title":"Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/DB393","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!8n8md1Op_fo!!iMgn5uz8Sbv","dateLastActive":"2023-08-18T20:39:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":38,"score":null,"hot":3384771350,"url":"https:\/\/community.smartsheet.com\/discussion\/109156\/using-join-collect-to-collect-data-from-multiple-sheets-to-populate-in-1-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109156\/using-join-collect-to-collect-data-from-multiple-sheets-to-populate-in-1-cell","format":"Rich","lastPost":{"discussionID":109156,"commentID":391485,"name":"Re: Using Join(Collect to collect data from multiple sheets to populate in (1) cell","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391485#Comment_391485","dateInserted":"2023-08-18T20:39:19+00:00","insertUserID":164856,"insertUser":{"userID":164856,"name":"DB393","title":"Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/DB393","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!8n8md1Op_fo!!iMgn5uz8Sbv","dateLastActive":"2023-08-18T20:39:35+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-08-18T20:39:33+00:00","dateAnswered":"2023-08-18T20:28:12+00:00","acceptedAnswers":[{"commentID":391482,"body":"

No. In your formula from your post just before my last one, you were adding together 3 different joins. The first one has a different syntax from the 2nd and 3rd. The 2nd and 3rd have the correct syntax already.<\/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 with 3 criteria (2 of the criteria have formulas)

    Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a "High", "Medium", or "Low" value based on the calculated score in column…

  • Checkbox, Texts, RYG

    I am trying to create a formula based on checkbox and dropdown list column. The result I am looking for is for Column 3 to have either a Red or Green symbol based on if Column 1 is checked OR Column 2 includes a specific word. Column 2 is a drop down with multiple options. Is this possible?

  • Using Join(Collect to collect data from multiple sheets to populate in (1) cell

    I am looking to combine data from (3) sheets into one sheet. There will be 3 columns: Team, Assignment, and Notes The cells in the Team and Assignment columns from Sheet1, Sheet2 and Sheet3 are populated from a multi-select dropdown lists. Notes are free type. Example: p-id on the destination sheet is xx, Sheet1 and Sheet2…