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.

Pull Data from Master sheet to another sheet
Hello, I have a master vendor list sheet that contains a checkbox column for vendor type. I would like to create a new sheet that pulls over just the vendors that have the vendor type column checked. I need a separate sheet so I can use it for widget in my dashboard. I don't want to filter or sort the main master vendor…
使用的数据one sheet to fill in a cell on another
I need a cell to populate based on a cell in another sheet. If sheet 1 contains the same number as sheet 2 column 1 then print notes from sheet 1 column notes else leave blank. This is what I have so far =IF(HAS({Sheet 1 Column 1}, [sheet 2]1), {Sheet 1 notes}, " "). {Sheet 1 Column 1} Refrences the entire column is sheet…
Sheet formula efficiencies
Hi, I have a feeling that I should be using "@cell" or "@row" in a formula to make less calculations. Can anyone shed light on a better formula than the one I have started? Basically trying to determine if a date falls between two other dates through a range (i.e. find out which year/quarter that a specific dates falls…
COUNTIF CONTAINS(OR(
Can anyone help me with a formula for a multi-select column where I am counting the number of occurrences of two of the options? I think it would be a COUNTIFS(CONTAINS(OR( formula, but can't seem to get it to work. Thanks!
Return value in Parent Cell for Child value
I have been trying to use the Index & Match function to automatically populate in the Parent row based on the status of a Child row, however, the formula will not pick up the Child status. For example, I want the STATUS column of the Parent row to populate with the STATUS of the Child where the Child DOCUMENT TYPE = "Trust…
How to "actualize" or transfer dependencies of a completed task?
Task A Task B is dependent on Task A Task C is dependent on Task B For some reason, I was able to finish Task B and Task A was getting delayed. How do I: 1. Extend duration of Task A without impacting dates, duration or status of Task B? 2. Mark Task B complete, such that actual completion dates do not change irrespective…
Formula Assistance
Hi All A newbie to Smartsheets, so please bear with me. I have a formula that works on excel but not on Smartsheets….any ideas why? Excel: =IF(K44="Approved",SUM(F43-C44),IF(K44="Declined",F43)) Smartsheets: =if([Approver approval]43="Approved",sum([Days Remaining]42-[Annual Leave]43,if([Approver…
DATE function for drop down menus
Hi all, I am using a drop down including different years (2019, 2020, 2021, etc.) that I need for the card view. Based on the selected drop down value in Column 1 (e.g. 2020) I want to auto-populate a ‘date’ field with the first day of the year (e.g. 2020.01.01) in column 2. I tried to use the following: = DATE ([Column…
COUNTIFS with more than 2 criterion
Hi, I am trying to do a count for a field with 3 criteria. I want to do COUNTIFS( A = "x", B = "y", and C = "Z"). Is there a way to achieve this as COUNTIFS only allows you to do this with 2 criterion?
形式/Formulas
Hi, I have a Form/Dashboard on Smartsheet to book site induction on the project I am currently working on (see attached image). Just like every other Smartsheet forms, once the form is filled in it feeds into the sheet. Is it possible that once someone books an induction late (48 hours prior to the induction date) they get…

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公式手册模板!

=TODAY()) This worked for over a year but suddenly is giving an error message, any idea if something in the TODAY function changed?","snippet":"In a SUMIFS function I am using a date check to sum all cells that have an expiry date equal or greater than TODAY: =SUMIFS(Expiry:Expiry, >=TODAY()) This worked for over a year…","categoryID":322,"dateInserted":"2023-10-19T19:55:02+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T20:48:29+00:00","insertUserID":159456,"insertUser":{"userID":159456,"name":"jkt2001","url":"https:\/\/community.smartsheet.com\/profile\/jkt2001","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T20:45:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165153,"lastUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T20:46:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":34,"score":null,"hot":3395497411,"url":"https:\/\/community.smartsheet.com\/discussion\/111927\/today-function-randomly-stopped-working-in-my-summary-metric-formulas-how-do-i-fix","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111927\/today-function-randomly-stopped-working-in-my-summary-metric-formulas-how-do-i-fix","format":"Rich","lastPost":{"discussionID":111927,"commentID":400855,"name":"Re: TODAY Function randomly stopped working in my Summary Metric formulas, how do I fix?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400855#Comment_400855","dateInserted":"2023-10-19T20:48:29+00:00","insertUserID":165153,"insertUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T20:46:25+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-10-19T20:46:41+00:00","dateAnswered":"2023-10-19T20:38:18+00:00","acceptedAnswers":[{"commentID":400853,"body":"

@jkt2001,<\/p>

I would check all the ranges being used as criteria and ensure all the data is in the format expected.<\/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":111907,"type":"question","name":"How do you calculate a date based on text in another cell?","excerpt":"I need to Smartsheet to use the name of a company in another cell to determine which formula to use to calculate a date in another cell. I am trying to calculate the conversion date of a temp, and based on which agency we are using the fee structure is different. Some may convert at 120 days and others at 180 days. If…","snippet":"I need to Smartsheet to use the name of a company in another cell to determine which formula to use to calculate a date in another cell. I am trying to calculate the conversion…","categoryID":322,"dateInserted":"2023-10-19T15:10:51+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T16:08:07+00:00","insertUserID":168790,"insertUser":{"userID":168790,"name":"LMA","title":"Sr. Talent Acquisitions Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/LMA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T15:38:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168790,"lastUser":{"userID":168790,"name":"LMA","title":"Sr. Talent Acquisitions Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/LMA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T15:38:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3395461138,"url":"https:\/\/community.smartsheet.com\/discussion\/111907\/how-do-you-calculate-a-date-based-on-text-in-another-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111907\/how-do-you-calculate-a-date-based-on-text-in-another-cell","format":"Rich","lastPost":{"discussionID":111907,"commentID":400786,"name":"Re: How do you calculate a date based on text in another cell?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400786#Comment_400786","dateInserted":"2023-10-19T16:08:07+00:00","insertUserID":168790,"insertUser":{"userID":168790,"name":"LMA","title":"Sr. Talent Acquisitions Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/LMA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T15:38:47+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-10-19T19:20:18+00:00","dateAnswered":"2023-10-19T15:27:09+00:00","acceptedAnswers":[{"commentID":400776,"body":"

@LMA<\/a> <\/p>

Something like the below should work for you<\/p>

Hi Terri,<\/p>

The #UNPARSABLE error<\/a> is thrown when \"the formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.\"<\/p>

ISBLANK<\/a> is a formula of it's own. It is expecting an input value between parenthesis. Here is an updated version of your formula<\/p>

=IF(NOT(ISBLANK([Frequency L4]@row)), 1, 0)<\/p>

Here is a help article<\/a> which should help with your other question regarding parent\/children rows.<\/p>

Hope this helps!<\/p>

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