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.

Is there a formula that will work for this?
Please see attached screenshot In a separate field (that I haven’t yet created), I would want to know the % availability (based on 40hr work week) of each assigned resource based on each active request status.
If / and forumula
Hi I'm trying to get a formula that returns the following grades. High Distinction (HD) 85-100 Distinction (D) 75-84 Credit (C) 65-74 Pass (P) 50-64 Fail (F) <50 I've tried this a few different ways but keep getting the #UNPARSEABLE message. Appreciate any help with trying to get this formula to work. =IF(AND(Percentage1 <…
Human Resources Time Off Requests with Functionality
Hi, I found 2 templates on smartsheets for time off, but neither work, and I have attempted to customize to my needs and I am not able to figure it out. First I want a form that employees can submit their leave, Second create a workflow for approval, and Third be able to tally the balances automatically to the appropriate…
Automatically Adding Subtasks
I am working to create a Kanban Sheet and Cardview for continuous projects that we do in our office. We have the same subtasks associated with each client through 3 various phases. Is there a way that when I add a new client or Primary/Parent Task, that the same subtasks are automatically added? Thanks for your help!
Moved: Nested IF AND & OR
This discussion has been moved.
Formula to calculate the next Friday after today
I have the formula below in Excel that calculates the next friday from today's date; so that any time the sheet is opened before a friday it will automatically insert the date for the coming friday, and when that date has passed and the sheet is re opened it will automatically insert the next fridays date in the week…

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!

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

Watch video

@Beth Fantozzi 1<\/a> <\/p>

I got you - use this:<\/p>

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(NOT(ISNUMBER([Quote Amount]@row)), NOT(ISNUMBER([Actual Amount]@row))), 0, IF(ISNUMBER([Actual Amount]@row), [Budgeted Amount]@row - [Actual Amount]@row, [Budgeted Amount]@row - [Quote Amount]@row)))<\/p><\/div><\/div>

It ends up looking like this:<\/p>

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

<\/p>

_____________________________________________________________________________________________<\/p>

👨🏼‍💻 Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/strong><\/a> | Smartsheet Aligned Gold Partner<\/strong><\/p>

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and\/or ❤️Awesome).<\/em><\/p>

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p>

▶️ Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>

Hello @Michelle Choate 2<\/a> !<\/p>

Yes, use this formula:<\/p>

=IF([Date of Record]@row < DATE(2023, 7, 14), 43, IF([Date of Record]@row <= DATE(2023, 8, 22), 46))<\/p><\/div><\/div>

It looks like this:<\/p>

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

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>

<\/p>
You can keep adding IF() statements to this for additional date limits and outputs.<\/p>
Hope that helps!<\/p>
___________________________________________________________________________________________<\/p>
👨🏼‍💻 Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/strong><\/a> | Smartsheet Aligned Gold Partner<\/strong><\/p>
If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and\/or ❤️Awesome).<\/em><\/p>
🆘 Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p>
▶️ Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>
PS - If you have a follow up response attention use @Dan Palenchar<\/a> so I get notified of your reply!<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":111278,"type":"question","name":"Formula for Color Coding Cells when 2 conditions haven't been met","excerpt":"Working a project plan and looking for a logical formula to color code the Status column: Highlight\/Color code the Status field if the start date is in the past and the status is \"To be Initiated\", or if the end date is in the past and the status is not \"Completed\" Thank you!","snippet":"Working a project plan and looking for a logical formula to color code the Status column: Highlight\/Color code the Status field if the start date is in the past and the status is…","categoryID":322,"dateInserted":"2023-10-05T18:48:28+00:00","dateUpdated":"2023-10-05T20:22:17+00:00","dateLastComment":"2023-10-06T13:27:41+00:00","insertUserID":140828,"insertUser":{"userID":140828,"name":"MarianneD617","url":"https:\/\/community.smartsheet.com\/profile\/MarianneD617","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-10-05T22:16:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":140828,"lastUserID":140828,"lastUser":{"userID":140828,"name":"MarianneD617","url":"https:\/\/community.smartsheet.com\/profile\/MarianneD617","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-10-05T22:16:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":30,"score":null,"hot":3393131769,"url":"https:\/\/community.smartsheet.com\/discussion\/111278\/formula-for-color-coding-cells-when-2-conditions-havent-been-met","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111278\/formula-for-color-coding-cells-when-2-conditions-havent-been-met","format":"Rich","lastPost":{"discussionID":111278,"commentID":398875,"name":"Re: Formula for Color Coding Cells when 2 conditions haven't been met","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398875#Comment_398875","dateInserted":"2023-10-06T13:27:41+00:00","insertUserID":140828,"insertUser":{"userID":140828,"name":"MarianneD617","url":"https:\/\/community.smartsheet.com\/profile\/MarianneD617","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-10-05T22:16:06+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\/JLQU8D8JYSX5\/screenshot-2023-10-05-at-2-42-34-pm.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JLQU8D8JYSX5\/screenshot-2023-10-05-at-2-42-34-pm.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JLQU8D8JYSX5\/screenshot-2023-10-05-at-2-42-34-pm.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JLQU8D8JYSX5\/screenshot-2023-10-05-at-2-42-34-pm.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JLQU8D8JYSX5\/screenshot-2023-10-05-at-2-42-34-pm.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JLQU8D8JYSX5\/screenshot-2023-10-05-at-2-42-34-pm.png"},"alt":"Screenshot 2023-10-05 at 2.42.34 PM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T13:36:08+00:00","dateAnswered":"2023-10-05T21:48:44+00:00","acceptedAnswers":[{"commentID":398779,"body":"
Hi @MarianneD617<\/a> <\/p>
I hope you're well and safe!<\/p>
The easiest way is to create two Conditional Formatting Rules.<\/p>
\n
\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n
Did that work\/help? <\/p>
I hope that helps!<\/p>
Be safe, and have a fantastic week!<\/p>
Best,<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[]}">

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags