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.

Discussion List

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Give this a try:<\/p>

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Canceled\"), \"Canceled\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = \"Complete\", @cell = \"Canceled\", @cell = \"On Hold\")), \"Complete\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Not Started\"), \"Not Started\", IF(COUNTIFS(CHLDREN(), @cell = \"Upcoming\")> 0, \"Upcoming\", \"In Progress\"))))<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108218,"type":"question","name":"Help with \"AND\" formula","excerpt":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an \"Invalid Data type\". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, \"2019\", {Product},…","snippet":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm…","categoryID":322,"dateInserted":"2023-07-27T20:23:56+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":156865,"lastUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3381039668,"url":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108218,"commentID":387766,"name":"Re: Help with \"AND\" formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387766#Comment_387766","dateInserted":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+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-28T12:46:42+00:00","dateAnswered":"2023-07-27T22:53:31+00:00","acceptedAnswers":[{"commentID":387693,"body":"

AVERAGEIF and AND do not play well together. Try this one, it collects all values that meet your criteria and then averages them. I am assuming {Complexity Score New} is the range with the values you would like to average.<\/p>

=AVG(COLLECT({Complexity Score New}, {Complexity Year}, \"2019\", {Product}, \"ESP\"))<\/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":108203,"type":"question","name":"Query string is not working for multiple variables","excerpt":"Hi, I am trying to pre-populate a form with multiple variables. I am trying to use a formula with the query string (see below). Only the first variable shows up correctly on the form. It is not picking up on the other 2. What am I doing wrong?","snippet":"Hi, I am trying to pre-populate a form with multiple variables. I am trying to use a formula with the query string (see below). Only the first variable shows up correctly on the…","categoryID":322,"dateInserted":"2023-07-27T17:34:16+00:00","dateUpdated":"2023-07-28T09:39:53+00:00","dateLastComment":"2023-07-28T14:06:19+00:00","insertUserID":151597,"insertUser":{"userID":151597,"name":"Vera","url":"https:\/\/community.smartsheet.com\/profile\/Vera","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7Htb-wQeSzo!u-9aZ6AooXQ!ulvrErhIp_U","dateLastActive":"2023-07-28T14:16:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":151597,"lastUser":{"userID":151597,"name":"Vera","url":"https:\/\/community.smartsheet.com\/profile\/Vera","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7Htb-wQeSzo!u-9aZ6AooXQ!ulvrErhIp_U","dateLastActive":"2023-07-28T14:16:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3381033635,"url":"https:\/\/community.smartsheet.com\/discussion\/108203\/query-string-is-not-working-for-multiple-variables","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108203\/query-string-is-not-working-for-multiple-variables","format":"Rich","lastPost":{"discussionID":108203,"commentID":387780,"name":"Re: Query string is not working for multiple variables","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387780#Comment_387780","dateInserted":"2023-07-28T14:06:19+00:00","insertUserID":151597,"insertUser":{"userID":151597,"name":"Vera","url":"https:\/\/community.smartsheet.com\/profile\/Vera","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7Htb-wQeSzo!u-9aZ6AooXQ!ulvrErhIp_U","dateLastActive":"2023-07-28T14:16:45+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\/DROUXUAPDHJF\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T14:08:28+00:00","dateAnswered":"2023-07-27T18:54:53+00:00","acceptedAnswers":[{"commentID":387621,"body":"

You need an & before each field name after the first and it looks like anything being populated after the initial field isn't having the special characters (in this case the space) percent encoded. Try this:<\/p>


<\/p>

=\"form_url?\" + SUBSTITUTE(<\/strong>\"Sales_Rep=\" + [Sales_Rep]@row + \"&<\/strong>Current_Career_Level=\" + [Current_Career_Level]@row + \"&<\/strong>Career_Level_Applying_For=\" + [Career_Level_Applying_For]@row, \" \", \"%20\")<\/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":[]}">

Trending in Formulas and Functions