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.

Discussion List

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@PR12x3<\/a> <\/p>


<\/p>

Your Today call in your first syntax is incorrect. instead of AND(Today), it should be AND(TODAY()<\/p>

You also do not need @row after the Today() function. Today is pulling from your local date on your workstation<\/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":111965,"type":"question","name":"Using a cell instead of a range for IF statement?","excerpt":"Hi all - looking to average a range based on whether or not a neighboring cell has a value of greater than zero - all I get are errors. any help is appreciated, TIA","snippet":"Hi all - looking to average a range based on whether or not a neighboring cell has a value of greater than zero - all I get are errors. any help is appreciated, TIA","categoryID":322,"dateInserted":"2023-10-20T16:14:41+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T17:54:47+00:00","insertUserID":132741,"insertUser":{"userID":132741,"name":"Jaime Holder","url":"https:\/\/community.smartsheet.com\/profile\/Jaime%20Holder","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZJM3D1OZEHQY\/nLRP1PJDG6PRE.jpg","dateLastActive":"2023-10-20T16:44:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-20T22:40:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3395644768,"url":"https:\/\/community.smartsheet.com\/discussion\/111965\/using-a-cell-instead-of-a-range-for-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111965\/using-a-cell-instead-of-a-range-for-if-statement","format":"Rich","lastPost":{"discussionID":111965,"commentID":401001,"name":"Re: Using a cell instead of a range for IF statement?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401001#Comment_401001","dateInserted":"2023-10-20T17:54:47+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-10-20T22:40:43+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\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/TM6EY8YG2JWJ\/screenshot-2023-10-20-121150.png"},"alt":"Screenshot 2023-10-20 121150.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-20T16:45:49+00:00","dateAnswered":"2023-10-20T16:42:30+00:00","acceptedAnswers":[{"commentID":400988,"body":"

@Jaime Holder<\/a> <\/p>

Try this:<\/p>

=IF([# of SO's]@row > 0, AVG([%age]$1:[%age]8), \" \")<\/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":111956,"type":"question","name":"Anyone able to help figure out this formula?","excerpt":"I would very much appreciate anyone's help on this. I have moved the brackets around and changed anything I can think of, but it's coming up \"unparseable\". The column headers and sheet references are right. =IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up…","snippet":"I would very much appreciate anyone's help on this. I have moved the brackets around and changed anything I can think of, but it's coming up \"unparseable\". The column headers and…","categoryID":322,"dateInserted":"2023-10-20T14:01:29+00:00","dateUpdated":null,"dateLastComment":"2023-10-20T15:20:24+00:00","insertUserID":162047,"insertUser":{"userID":162047,"name":"Nat","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Nat","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/TGR5VC4AXHS3\/nABTHIGSKZB11.jpg","dateLastActive":"2023-10-20T15:19:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"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-10-20T15:56:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":33,"score":null,"hot":3395628713,"url":"https:\/\/community.smartsheet.com\/discussion\/111956\/anyone-able-to-help-figure-out-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111956\/anyone-able-to-help-figure-out-this-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111956,"commentID":400974,"name":"Re: Anyone able to help figure out this formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400974#Comment_400974","dateInserted":"2023-10-20T15:20:24+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-10-20T15:56:56+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-20T15:19:38+00:00","dateAnswered":"2023-10-20T15:09:09+00:00","acceptedAnswers":[{"commentID":400970,"body":"

That's some progress I guess! Next up: <\/p>

The OR statement isn't closed off and I'm not sure you need the AND portion either as you've only a single expression in there. Something along the lines of:<\/p>

=IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up sheet DATE OF DEPARTURE}, OR(@cell = \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", @cell >= [Quarter Start]@row), {HR mock-up sheet JOB TYPE}, “Permanent FT”)))<\/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":[]}">

Trending in Formulas and Functions

  • Added TODAY() to a formula that works and get an error

    Goal is to have a symbol represent how much newer the forecasted date is to TODAY. Any help would be much appreciated.

  • Using a cell instead of a range for IF statement?

    Hi all - looking to average a range based on whether or not a neighboring cell has a value of greater than zero - all I get are errors. any help is appreciated, TIA

  • Anyone able to help figure out this formula?

    I would very much appreciate anyone's help on this. I have moved the brackets around and changed anything I can think of, but it's coming up "unparseable". The column headers and sheet references are right. =IF(TODAY() >= [Quarter Start]@row, COUNTIFS({HR mock-up sheet START DATE}, @cell <= [Quarter End]@row, {HR mock-up…