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.

Disappearing Formulas in Locked Column
I have a locked column with a formula used for a calculation. The formula is dragged all the way down. The formula keeps disappearing within blank cells. The activity log says it is changed by cell link. I keep dragging down the formulas and saving, but it just resets again. Any ideas?
Count if Formula help for a count within a date range
Here is my current formula - =COUNTIFS({Retail MarketingLead}, [Primary Column]@row, {Actual in MarketStart Date}, @cell <= TODAY()) I need to capture the count for April 1, 2020 - March 31, 2021
Have a specific cell auto increment up as a new number is added
Per the example below, column "Total UG Footage Complete", I would like have the cell with "50" in it auto increment up when the box is highlighted and I added another 50 for it to show 100. Can that be done?
WeekNumber Across Years
I have been able to find success at using the weeknumber formula and combining that with a 'past due' vs 'due this week' vs 'due next week' indicator. I have a separate sheet that identifies what 'this week' vs 'next week' vs 'last week' is by week number so that I just need to change it in 1 place for all of my projects.…
IF(ISERROR(VLOOKUP #No Match Question
我正在做一个跨多个表和kn vlookupow i will have some #No Match results, however i want to make sure those rows that do not match have a specific response in the cell which is a blank result. How can i accomplish this? Current formula i am using: =IF(ISERROR(VLOOKUP([BE GEO ID]1, {FY21 Radius Partner…
Date formulas
I am very new to this and don't work with formulas often. I am trying to create a formula that will update the finish date automatically based on a given timeframe. For example start date is 10/12 next column gives time expected to complete task 6 weeks. I want to create a formula that will autofill the completion date in…
How to link same type of multiple data on a single row from one Sheet to another.
Good morning, I have two Smartsheets that I want to link and get some data. Smartsheet 1 has the person's name (and some other info) and Smartsheet 2 has their certificate data. I'll add a Cert column on Smartsheet 1, to link the cert data from Smartsheet 2, however, some names have more than one certs. How can I link…
Counting totals with checkbox rows
Hi there, I am trying to set up a formula where it will add up all of the "buying price" column where "sold" is checked. I want to see the total value of all sold units, based on the check box approach. See attached image. Can any body advise how to achieve this? I've been messing around now for a long time, and I cannot…
VLOOKUP with conditional criteria.
As mentioned in a couple of previous threads, I've created a complicated cross-sheet formula with several different reports. What I'm trying to do now is have a sheet populate whatever information is found in Range 1 if it meets the conditions mentioned. So, if anything is entered into Range 1 (Outlet) with the condition…
Excel Translation
How do I translate this to Smartsheet? =IFS(C3="black",1,C3="blue",2,C3="red",3,C3="green",4) I realize there is no IFS function an that I need to nest the information, but I can not get the formula worked out. =IF[Color]1=Black,"1"

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

Hi @Dpres<\/a> <\/p>

If the staff are only working at an option in 1 column at a time you can do this with a simple JOIN:<\/p>

=JOIN([Austin Clinics]@row:[Rio Grande Clinics]@row)<\/p>

This will combine all of the columns, but as 3 are blank it will just show the 1 they are at.<\/p>

If this isn't the case or I've missed something then let me know and we'll see if we can puzzle an alternative out! 😊<\/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":[]},{"discussionID":107502,"type":"question","name":"Using CONTAINS with JOIN(COLLECT)","excerpt":"Hello! I'm trying to add CONTAINS to an existing JOIN(COLLECT) formula, and I can't seem to get it to work. The existing (working) formula is: =JOIN(COLLECT({RAID Log Title}, {RAID Log Flag}, \"1\", {RAID Log Category}, \"Issue\", {RAID Log Top 10 Priority}, [Priority Name]@row), \", \"). The formula I've come up with to add…","snippet":"Hello! I'm trying to add CONTAINS to an existing JOIN(COLLECT) formula, and I can't seem to get it to work. The existing (working) formula is: =JOIN(COLLECT({RAID Log Title},…","categoryID":322,"dateInserted":"2023-07-12T15:40:14+00:00","dateUpdated":"2023-07-12T16:01:55+00:00","dateLastComment":"2023-07-12T17:05:17+00:00","insertUserID":161010,"insertUser":{"userID":161010,"name":"scisar","url":"https:\/\/community.smartsheet.com\/profile\/scisar","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T17:41:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":91566,"lastUserID":161010,"lastUser":{"userID":161010,"name":"scisar","url":"https:\/\/community.smartsheet.com\/profile\/scisar","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T17:41:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3378359131,"url":"https:\/\/community.smartsheet.com\/discussion\/107502\/using-contains-with-join-collect","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107502\/using-contains-with-join-collect","format":"Rich","lastPost":{"discussionID":107502,"commentID":384838,"name":"Re: Using CONTAINS with JOIN(COLLECT)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384838#Comment_384838","dateInserted":"2023-07-12T17:05:17+00:00","insertUserID":161010,"insertUser":{"userID":161010,"name":"scisar","url":"https:\/\/community.smartsheet.com\/profile\/scisar","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T17:41: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-12T16:55:06+00:00","dateAnswered":"2023-07-12T16:08:17+00:00","acceptedAnswers":[{"commentID":384815,"body":"

Hi @scisar<\/a>,<\/p>

Your CONTAINS section of the formula is incorrect, I think. It should be: <\/p>

...CONTAINS([Priority Name]@row, @cell)...<\/p>

Hi,<\/p>

<\/p>

You don't really need to add \"@cell\" for the criteria in region and service line and you will need to change the row index to 1 instead of 0 to get the value.<\/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":[]}">