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.

Checkbox formula with percentage
Smartsheet guru's-- I'm still learning how to use all of Smartsheet's assets. I am trying to show a percentage for checked boxes. Can someone help me with the formula part? Thank you!
Formula to create a number assignment catalog
Hello, I am trying to create a number assignment catalog that is automated to speak to a smartsheet that has my full collection of products. The goal is when creating a new product anyone could fill out a form select the category numbers that will define the department and then select a a number that will identify the…
Pulling data from a sheet
Good Morning, I have a report that I do monthly that records the data from the following sheet. At the end of the month I usually filter columns and manually count errors for each responsible party and move them to a another sheet that records each persons errors for the whole year. I record data for about 30 individuals…
sequential number for each parent row - column formula
Hello im trying to generate a sequential number for each parent row. I can do this using an helper column called parent that flags each parent record with 1 then the formula @row, 1) this won't convert to a column formula because of the explicit row references used to define the range. can it be done. any help appreciated…
Accounting for the word "Perpetual" in a date field when there is no end date
I am currently running this formula: =[Contract Expiration Date]@row - [Notify days to renew/cancel]@row to calculate the date when we need to review a contract. It essentially takes the end date of the contract and subtracts the numbers of days specified in the contract if you want to cancel. We have some contracts with…
Column Formula not selectable as Gantt Label
Hi Folks, Is this a bug? For Gantt chart labels, I use a column that concatenates and formats the task and end dates; call this column "Gantt Label". I select this as my Gantt label and it looks great. To prevent my formula from getting corrupted or broken, I convert "Gantt Label" to a Column Formula. Here's where it gets…
URL Query string did not move with "Save as New" How to remedy?
I used a test workspace to create 2 data base sheets. One to hold existing info and one to hold newly created info based on existing. Users create this new data by clicking a URL to get to a form. I created a form and appended a query string to the form URL such that when a person clicks the URL, information from the URL…
What criteria use to determine a "current user".
I have a report that surfaces information based on a current user filter. It is filtered on a contact column. The issue is, my users are all over the map with logon ids. We have two sets of emails in my company, internal and external. They all map to the same email client, so to the user it is transparent. In addition some…
Taking into account month AND year
Hi everyone, I have two columns. One is the date that the form was submitted, and the other tells us which quarter we received it. Our fiscal year is from April 1 to March 31. This formula works great, but I've realized it won't account for the change in year. Currently, we are in 2020/21, but as of April 1, we will be in…
Using COUNTIFS with CONTAINS
When I try to incorporate a CONTAINS into a COUNTSIFS i keep getting errors Works fine: =COUNTIFS({JSA - Daily Job Start Safety Form Range 1}, Date@row, {JSA - Daily Job Start Safety Form Range 2}, $Date$1) Incorporate CONTAINS : These are 2 i tried =COUNTIFS({JSA - Daily Job Start Safety Form Range 1}, Date@row, {JSA -…

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

You are basically looking for duplicates. Try something like this in a flag type column:<\/p>

=IF(COUNTIFS([Assigned To]:[Assigned To], @cell = [Assigned To]@row, [Due Date]:[Due Date], @cell = [Due Date]@row)> 1, 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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107659,"type":"question","name":"How to make Parent helper column reflect values based on Children values with multiple conditions","excerpt":"I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column Parent Proficiency to be based on CHILDREN values in Proficiency column Levels: Untrained In Process Trained Proficient Preceptor Parent helper row…","snippet":"I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column…","categoryID":322,"dateInserted":"2023-07-17T01:23:45+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T03:11:33+00:00","insertUserID":157192,"insertUser":{"userID":157192,"name":"decrom","url":"https:\/\/community.smartsheet.com\/profile\/decrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T03:18:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-07-17T17:49:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3379122318,"url":"https:\/\/community.smartsheet.com\/discussion\/107659\/how-to-make-parent-helper-column-reflect-values-based-on-children-values-with-multiple-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107659\/how-to-make-parent-helper-column-reflect-values-based-on-children-values-with-multiple-conditions","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107659,"commentID":385499,"name":"Re: How to make Parent helper column reflect values based on Children values with multiple conditions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385499#Comment_385499","dateInserted":"2023-07-17T03:11:33+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-07-17T17:49:51+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\/8JA27C48KJIR\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-17T02:55:22+00:00","dateAnswered":"2023-07-17T02:36:04+00:00","acceptedAnswers":[{"commentID":385496,"body":"

Hey @decrom<\/a> <\/p>

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?<\/p>

Please let me know what we need to tweak<\/p>

Kelly<\/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":[]}">

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags