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

Want to practice working with formulas directly in Smartsheet?

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

If you change the \"PP01\" in your formula to [Unit]@row, then it will reference the contents of the Unit cell on the row.<\/p>

You can then use this as a column formula, though you may wish to enfold it in an IFERROR so you don't get errors on the month row at the top, for example.<\/p>

=IFERROR(COUNTIFS({1. LOS, ADR and Occupancy data Range 6}, [Unit]@row, {1. LOS, ADR and Occupancy data Range 7}, \"1\"),\"//www.santa-greenland.com/community/categories/formulas-and-functions/\")<\/p>

Hope this helps, but if you've any problems\/questions then just post! 🙂<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":111722,"type":"question","name":"Calculating a fee based on QTY delivered and total fee outstanding","excerpt":"I am looking to calculate a fee amount based on total fee and qty delivered. For example, if I have two items on a PO, but only one delivers, the Fee to be Paid would only be half of the total. If all items deliver, the \"Total Admin Fee\" would copy over to \"Admin Fee to be Paid\". In the snip below, for the highlighted row,…","snippet":"I am looking to calculate a fee amount based on total fee and qty delivered. For example, if I have two items on a PO, but only one delivers, the Fee to be Paid would only be half…","categoryID":322,"dateInserted":"2023-10-16T18:20:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-17T12:23:43+00:00","insertUserID":168047,"insertUser":{"userID":168047,"name":"Stitched","url":"https:\/\/community.smartsheet.com\/profile\/Stitched","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-17T12:21:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168047,"lastUser":{"userID":168047,"name":"Stitched","url":"https:\/\/community.smartsheet.com\/profile\/Stitched","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-17T12:21:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3395027076,"url":"https:\/\/community.smartsheet.com\/discussion\/111722\/calculating-a-fee-based-on-qty-delivered-and-total-fee-outstanding","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111722\/calculating-a-fee-based-on-qty-delivered-and-total-fee-outstanding","format":"Rich","lastPost":{"discussionID":111722,"commentID":400295,"name":"Re: Calculating a fee based on QTY delivered and total fee outstanding","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400295#Comment_400295","dateInserted":"2023-10-17T12:23:43+00:00","insertUserID":168047,"insertUser":{"userID":168047,"name":"Stitched","url":"https:\/\/community.smartsheet.com\/profile\/Stitched","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-17T12:21:27+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\/H3ITPEK2WVAB\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/H3ITPEK2WVAB\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/H3ITPEK2WVAB\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/H3ITPEK2WVAB\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/H3ITPEK2WVAB\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/H3ITPEK2WVAB\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-17T12:46:33+00:00","dateAnswered":"2023-10-17T02:54:19+00:00","acceptedAnswers":[{"commentID":400259,"body":"

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

Try this in your [Admin Fee to be Paid] column<\/p>

=[Total Admin Fee]@row * [Quantity Delivered]@row \/ [Quantity Ordered]@row <\/p>

Will this work for you?<\/p>

Kelly@Robert Harrison<\/a><\/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":111731,"type":"question","name":"Trying to create a formula that determines the current season","excerpt":"I've got a formula that generates the current quarter and one that calculates the current year. Now I'm trying to write an IF statement that creates a value that says S23 by reading if its Q1 or Q2 and then adding the 23 from the current year (2023). This is my current formula, can anyone explain why it's not working or…","snippet":"I've got a formula that generates the current quarter and one that calculates the current year. Now I'm trying to write an IF statement that creates a value that says S23 by…","categoryID":322,"dateInserted":"2023-10-16T21:22:42+00:00","dateUpdated":"2023-10-16T21:37:23+00:00","dateLastComment":"2023-10-16T21:49:20+00:00","insertUserID":163068,"insertUser":{"userID":163068,"name":"Matthew L","title":"Marketing Technology Strategist","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20L","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pDfp6e_dqLI!k5joH9_8Rco!8kFKi41mow7","dateLastActive":"2023-10-16T21:49:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":163068,"lastUserID":163068,"lastUser":{"userID":163068,"name":"Matthew L","title":"Marketing Technology Strategist","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20L","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pDfp6e_dqLI!k5joH9_8Rco!8kFKi41mow7","dateLastActive":"2023-10-16T21:49:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":16,"score":null,"hot":3394984922,"url":"https:\/\/community.smartsheet.com\/discussion\/111731\/trying-to-create-a-formula-that-determines-the-current-season","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111731\/trying-to-create-a-formula-that-determines-the-current-season","format":"Rich","tagIDs":[254,279,281,440],"lastPost":{"discussionID":111731,"commentID":400237,"name":"Re: Trying to create a formula that determines the current season","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400237#Comment_400237","dateInserted":"2023-10-16T21:49:20+00:00","insertUserID":163068,"insertUser":{"userID":163068,"name":"Matthew L","title":"Marketing Technology Strategist","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20L","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pDfp6e_dqLI!k5joH9_8Rco!8kFKi41mow7","dateLastActive":"2023-10-16T21:49:35+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-16T21:49:33+00:00","dateAnswered":"2023-10-16T21:49:20+00:00","acceptedAnswers":[{"commentID":400237,"body":"

Figured it out, I was just missing @row on all my column values. Here's the working formula if anyone needs. <\/p>

=IF(OR([Current Quarter]@row = \"Q1\", [Current Quarter]@row = \"Q2\"), \"S\" + RIGHT([Current Year]@row, 2), \"F\" + RIGHT([Current Year]@row, 2))<\/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"},{"tagID":279,"urlcode":"marketing","name":"Marketing"},{"tagID":281,"urlcode":"support","name":"Support"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"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