Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template查看100 +公式,包括词汇的夏娃ry 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.

Formula to change line item to 'at risk' when due date is passed
Goal: When a finish date is passed on a line item, I want the status column to change to 'at risk'.
I would like to count the aging but I'm getting #UNPARSEABLE in this formula.
ISBLANK or 0 (ZERO) : Formula for a column
Good afternoon, I have the following formula that is populating my "HRS Left" column =IF(OR(ISBLANK(BAC@row), ISBLANK(HTD@row)), BAE@row, SUM(BAC@row - HTD@row)). But I need to be able to write the formula so that the "HRS Left" column will populate if my "HTD" or "BAC" column is "0" (zero) or ISBLANK Thank you
Generating a column value until another column is filled in
Good afternoon, I am looking to see if there is even a formula for what we are looking to do. We have an "HRS Left" column that projects the hours left on a scope item with this formula =SUM(BAC@row - HTD@row) However!, until we actually start working on that scope we have a "BAE"column that carries our Budget At Estimate…
Formula for Status
I would like to auto create a status based on an end date, completed date, cancelled checkbox or on hold checkbox. If end date is in the future an neither checkbox is selected then "Open". If the end date is in the past then "Overdue". If complete date has been entered then "Complete". If cancelled checked then…
Unparseable Error
Dear Smartsheet Community, Very new to Smartsheet and having trouble with 2 formulas that work in Excel. =IF([Date Approved] = 0, [Date Calculation],"Completed") =NOW() - [Date of Submission] Could you kindly let me know if the formula structure is incorrectly done? Would appreciate your input. Thank you. Best, ~ Rowenna
Automate Risk Flag
Hi All, I am struggling with trying to find and create a formula to automate the Risk Flag. I'd like to use simple logic to provide a flag if any of the ancestor rows have one of the following values The two columns are Status, with dropdowns, and Risk Level with High, Medium, Low Symbols, and I would like to return a red…
Hi, can anyone help me to get my Green/Yellow/Red/Blue Status to work correctly?
I have to write a green/yellow/red/blue status indicator with a risk override field as well. The concept is that if the Risk Override field is populated then that should be the status. The risk override field will be a drop down between Red and Yellow. If that is not populated, then it should be Blue if % complete is 1…
How to use cell input as column name in formula?
你好,是否可以使用一个细胞值填充in the column name within a formula? We add a new column each week. I would like to do a simple COUNT function, for example. Instead of updating the formula each week, I'd like to update the value in a cell that the formula will reference. So instead of…
Using SUMIF to have a blank cell if data comes back zero
I am pulling data from another sheet. I am using a SUMIF to sum things off a certain date and if it meets a certain name. When it has no data to bring back, it shows a zero and I would like for the cell to be blank so that my charts do not show a lot of zeros. How can I do this? Thanks =SUMIFS({AIC-Diverts 2020 Range 1},…

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 theFormula Handbook template!

Enter to Win a FREE Conference Pass to ENGAGE 2023

Learn more

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

If the staff are only working at an option in 1 column at a time you can do this with a simple JOIN:<\/p>

=JOIN([Austin Clinics]@row:[Rio Grande Clinics]@row)<\/p>

This will combine all of the columns, but as 3 are blank it will just show the 1 they are at.<\/p>

If this isn't the case or I've missed something then let me know and we'll see if we can puzzle an alternative out! 😊<\/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":[]},{"discussionID":107502,"type":"question","name":"Using CONTAINS with JOIN(COLLECT)","excerpt":"Hello! I'm trying to add CONTAINS to an existing JOIN(COLLECT) formula, and I can't seem to get it to work. The existing (working) formula is: =JOIN(COLLECT({RAID Log Title}, {RAID Log Flag}, \"1\", {RAID Log Category}, \"Issue\", {RAID Log Top 10 Priority}, [Priority Name]@row), \", \"). The formula I've come up with to add…","snippet":"Hello! I'm trying to add CONTAINS to an existing JOIN(COLLECT) formula, and I can't seem to get it to work. The existing (working) formula is: =JOIN(COLLECT({RAID Log Title},…","categoryID":322,"dateInserted":"2023-07-12T15:40:14+00:00","dateUpdated":"2023-07-12T16:01:55+00:00","dateLastComment":"2023-07-13T08:27:55+00:00","insertUserID":161010,"insertUser":{"userID":161010,"name":"scisar","url":"https:\/\/community.smartsheet.com\/profile\/scisar","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T17:41:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":91566,"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-07-13T08:28:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3378415089,"url":"https:\/\/community.smartsheet.com\/discussion\/107502\/using-contains-with-join-collect","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107502\/using-contains-with-join-collect","format":"Rich","lastPost":{"discussionID":107502,"commentID":384965,"name":"Re: Using CONTAINS with JOIN(COLLECT)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384965#Comment_384965","dateInserted":"2023-07-13T08:27:55+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-07-13T08:28:17+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-12T16:55:06+00:00","dateAnswered":"2023-07-12T16:08:17+00:00","acceptedAnswers":[{"commentID":384815,"body":"

Hi @scisar<\/a>,<\/p>

Your CONTAINS section of the formula is incorrect, I think. It should be: <\/p>

...CONTAINS([Priority Name]@row, @cell)...<\/p>

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

<\/p>

Are you trying to get a count if the cost savings check is checked and the date is between 1st January to 31st December 2023? The formula should be =COUNTIFS({cost savings check}, 1, {OPENED}, >=DATE(2023,1,1),{OPENED},<=DATE(2023,12,31))<\/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"}]}],"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":[]}">