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.

Check what Support you have access to based on your Plan
Overview of Smartsheet support hours and resources

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.

1,1,0) . The problem is that this doesn't work for…","categoryID":322,"dateInserted":"2021-01-07T21:46:36+00:00","dateUpdated":null,"dateLastComment":"2021-01-13T00:20:17+00:00","insertUserID":130428,"insertUser":{"userID":130428,"name":"Eric Busse","url":"https:\/\/community.smartsheet.com\/profile\/Eric%20Busse","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wSvAsL7h0Pw!pOlBKWLyqbo!mu8v4UriiSw","dateLastActive":"2021-01-13T00:55:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":126171,"lastUser":{"userID":126171,"name":"Mark Cronk","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Cronk","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9UVGVH5TN00I\/nO060PSRF4UVU.jpg","dateLastActive":"2023-05-10T19:07:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":172,"score":null,"hot":8,"url":"https:\/\/community.smartsheet.com\/discussion\/74630\/is-it-possible-to-flag-duplicates-when-the-data-starts-with-a-0-zero","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/74630\/is-it-possible-to-flag-duplicates-when-the-data-starts-with-a-0-zero","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-01-12T22:16:39+00:00","dateAnswered":"2021-01-12T00:53:41+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":74764,"type":"question","name":"Formula Help","excerpt":"Not the greatest at creating formulas so I am looking for some assistance on this one. =IF(CONTAINS(\"0\", [Product Categories selected]@row:[Keyword field filled]@row), \"Complete\", \"Incomplete\") Here is what I am looking to do: If the four columns are all \"O\" then mark the status of Incomplete but if all four columns are…","categoryID":322,"dateInserted":"2021-01-12T22:16:54+00:00","dateUpdated":null,"dateLastComment":"2021-01-12T22:43:51+00:00","insertUserID":119359,"insertUser":{"userID":119359,"name":"Manuel Lemus","url":"https:\/\/community.smartsheet.com\/profile\/Manuel%20Lemus","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4RDP-MieUZQ!pqtMaRTZvro!NQoUb2fCEd8","dateLastActive":"2023-06-20T22:53:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":119359,"lastUser":{"userID":119359,"name":"Manuel Lemus","url":"https:\/\/community.smartsheet.com\/profile\/Manuel%20Lemus","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!4RDP-MieUZQ!pqtMaRTZvro!NQoUb2fCEd8","dateLastActive":"2023-06-20T22:53:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":53,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/74764\/formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/74764\/formula-help","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EUQBN3RHVNB7\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-01-12T22:43:07+00:00","dateAnswered":"2021-01-12T22:39:40+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Drop Down Lists
I'm creating a list of pre-definded codes for a drop down list e.g. 1, 2, 3 However I'd like to indicate to the user with the aid of a pop up or note saying what 1, 2 or 3 actually means to ensure they enter the right value. Instead of simply entering 1, 2 or 3 I could put a word description in but I'd further like to…
SUMIFS not calculating values-giving result as "0"
Hi, I am using following SUMIFS, though it is not throwing any error but it is not calculating the values in required range: =SUMIFS({Deliverables}, {Engg}, Resource@row, {Completion Month}, Month@row, {Status}, Status@row) Can anyone please help me know know, where I am missing on writing the argument? Thank you, Prajna
Flagging a Date 30 days past due?
I have a sheet with the following columns: Last Updated: date in which a customer profile was last edited Next Review Due: a date calculated as 4 months after the Last Updated Date -- this is the time at which an account team should review the customer profile data to ensure that it is still up-to-date and if any changes…
Can I create automated survey feedback to a user in Smartsheet
I'm new to using Smartsheet and I'm trying to design a survey with automated feedback to the user. I'm wondering if Smartsheet has the capability to do this. Here is an overview of what I'm trying to do. I have created a survey that will be going to out to certain company branches. The survey has 10 sections with a pass/no…
if and blank
嗨,我有一个约会,一个调查并完成a date that someone has contacted a customer and I'm looking to create a formula that highlights if the response date was over 3 days ago AND there has not been a successful call (the contact date column will be blank) then mark this as red, if the response data was over 3…
Nested IF statements
Can anyone assist me in where I am going wrong? =IF([GEAR.]@row = true, [C/N SQD]@row, IF(LTG@row = true, [C/N LIGHTING]@row), IF(AND([GEAR.]@row = true, LTG@row = true) = true, [PROJECT TOTAL]@row)) If checkbox1 = true, set value to cell 1, if checkbox2 = true, set value to cell2, if BOTH checkboxes = true, set value to…
Add How Many Names Appear in Column Without Using A Formula Using a Specific Name
I need to track first aid statistics to determine how many employees are responsible for first aid incidents, per month, per location. We have 275 employees and any of those names can appear in the spreadsheet during the year so I don't want to create a formula that will contain that many names. I want to pull information…
SUMIFS with Multiple Criteria
I have worked up SUMIFS that sums the Final COS for each of main Account Managers individually and for multiple codes in a contracted column that equate to a loss (this worked out fine): =SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "Doe", Contracted:Contracted, "not won") + SUMIFS([Final…
Is it possible to flag duplicates when the data starts with a 0 (zero)?
Hi! I have a sheet that tracks hundreds of cell phones, so it's really important to ensure existing records are updated rather than added as new rows. I can flag columns like Asset Tag and Phone Number with this formula =IF(COUNTIF([Asset Tag]:[Asset Tag],[Asset Tag]@row)>1,1,0) . The problem is that this doesn't work for…
Formula Help
Not the greatest at creating formulas so I am looking for some assistance on this one. =IF(CONTAINS("0", [Product Categories selected]@row:[Keyword field filled]@row), "Complete", "Incomplete") Here is what I am looking to do: If the four columns are all "O" then mark the status of Incomplete but if all four columns are…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

Learn more

=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31)) I've been noodling with it but can't seem to get it to work, help would be greatly appreciated!","snippet":"Hi!!! Tried this and it is #UNPARSEABLE: =COUNTIFS({cost savings check}, 1), AND({OPENED}, >=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31)) I've been noodling with it but can't…","categoryID":322,"dateInserted":"2023-07-12T21:43:48+00:00","dateUpdated":null,"dateLastComment":"2023-07-12T23:58:49+00:00","insertUserID":159929,"insertUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":159929,"lastUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3378406357,"url":"https:\/\/community.smartsheet.com\/discussion\/107529\/cross-sheet-formula-countifs-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107529\/cross-sheet-formula-countifs-and","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107529,"commentID":384936,"name":"Re: Cross Sheet Formula, COUNTIFS + AND?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384936#Comment_384936","dateInserted":"2023-07-12T23:58:49+00:00","insertUserID":159929,"insertUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+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-07-12T23:56:07+00:00","dateAnswered":"2023-07-12T23:44:08+00:00","acceptedAnswers":[{"commentID":384931,"body":"

Hi @MichelleR<\/a> <\/p>

<\/p>

Hi @swightman<\/a> <\/p>

<\/p>

Try this:<\/p>

=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT(), {Status}, OR(@cell = Metric21, @cell = Metric22, @cell = Metric23)<\/strong>)<\/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":[]}">