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.

Formula Help
Not the greatest at creating formulas so I am looking for some assistance on this one. =IF(CONTAINS("0", [Product Categories selected]@row:[Keyword field filled]@row), "Complete", "Incomplete") Here is what I am looking to do: If the four columns are all "O" then mark the status of Incomplete but if all four columns are…
Sumif/Sumifs question
I have a sheet that lists the number of awards that have been issued since the inception of the program. I want to create a report that breaks it out by year, award level (there are 3 different levels), and sum the total of that level. I have it by year, but adding the level I have not been able to figure out. Can someone…
Is it possible to Sum based off of status change in a different column?
For instance, if I had two columns, one is a number value and the other is a status value. if the status value is red and i wanted to sum just the values of the red statuses, is this possible by using a SUMIF Function. Thanks, Brendan
Functionality of dashboard with Forms on it
Hi, Created 3 forms for doing employee reviews with underlying grid sheets. 1 form is an Employee Self Review, 1 Form Employee Selection, and 1 Form Reviewer Form. Idea is a employee fills out self review, then sits with manager. Manager opens dashboard, selects Employee from Employee Selection Form, then clicks submit.…
How to convert my formulas to a column formula
How do I convert these formulas to a column formula? So the formulas work perfectly; however, I find myself constantly updating the source sheet when someone inserts or enters new records and then I have to go to my metrics sheet to make sure they are applied. I'm doing way too much manually work with these sheets and I…
将日期转换从一个细胞到另一个移动电话文本l
Hello, I am trying to convert a date in one cell to text in another cell, but have not been able to figure out an eloquent/simple formula to do so. The use case is: I have a planned start date column where the cells are populated with dates. I want to have that date translated into a Month label (drop down) in another…
Search for Duplicate Names within Two Separate Sheets
Good Morning, I am trying to enter a formula that will give a show me when a duplicate name is enter into one or the other sheet I am working on. We will be entering names daily into each sheet and we need to quick way to find if we have entered the same name in twice. Please let me know what formula you think will be…
Vlookup cell isn't multiplying with another cell.
Hi guys whenever I am trying to multiply my Vlookup cell with another cell it gives a Value error. I have linked web data to other sheets and have a vlookup for specific data in cell D6. In G6 I then want to multiply D6 by a value in F6 using =D6*F6, but a value error occurs. I have also tried copying and pasting the…
If statement with dates
Hi there, I am trying to put a formula in a symbol column (Yes, Hold, No options) and want the formula to make the symbol for "Hold" if the due date is within 14 days of TODAY. This is the formula I have come up with but I am not getting the "Hold" portion =IF([Due Date]@row < TODAY(), "No", IF([Due Date]@row < TODAY(-14),…
Trying to multiply 2 cells- one contains a long IF & VLOOKUP statement
I have the following problem. I am trying to multiply the value of two cells. One is a numeric value entered into a form the other is the answer to a nested IF ( VLOOKUP) formula. I keep getting and #INVALID VALUE error. I need the result to be a $ value that comes from the length of rental period selected multiplied by…

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 theFormula Handbook template!

Enter to Win a FREE Conference Pass to ENGAGE 2023

Learn more

=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31)) I've been noodling with it but can't seem to get it to work, help would be greatly appreciated!","snippet":"Hi!!! Tried this and it is #UNPARSEABLE: =COUNTIFS({cost savings check}, 1), AND({OPENED}, >=DATE(2023, 1, 1), {OPENED}, <=DATE(2023, 12, 31)) I've been noodling with it but can't…","categoryID":322,"dateInserted":"2023-07-12T21:43:48+00:00","dateUpdated":null,"dateLastComment":"2023-07-12T23:58:49+00:00","insertUserID":159929,"insertUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":159929,"lastUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3378406357,"url":"https:\/\/community.smartsheet.com\/discussion\/107529\/cross-sheet-formula-countifs-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107529\/cross-sheet-formula-countifs-and","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107529,"commentID":384936,"name":"Re: Cross Sheet Formula, COUNTIFS + AND?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384936#Comment_384936","dateInserted":"2023-07-12T23:58:49+00:00","insertUserID":159929,"insertUser":{"userID":159929,"name":"MichelleR","url":"https:\/\/community.smartsheet.com\/profile\/MichelleR","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wpHR_5jPu38!XIDR36jCCuc!jzAOxmieF6j","dateLastActive":"2023-07-12T23:53:49+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-12T23:56:07+00:00","dateAnswered":"2023-07-12T23:44:08+00:00","acceptedAnswers":[{"commentID":384931,"body":"

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

<\/p>

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

<\/p>

Try this:<\/p>

=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT(), {Status}, OR(@cell = Metric21, @cell = Metric22, @cell = Metric23)<\/strong>)<\/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":[]}">