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.

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.

\"//www.santa-greenland.com/community/categories/formulas-and-functions/\") Thanks!","categoryID":322,"dateInserted":"2022-11-08T22:37:18+00:00","dateUpdated":null,"dateLastComment":"2022-11-09T09:14:52+00:00","insertUserID":141331,"insertUser":{"userID":141331,"name":"deb_63_hydracor","url":"https:\/\/community.smartsheet.com\/profile\/deb_63_hydracor","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJzYn23TqUOGU8A4PW0HalCwzLK5YNQsVxHwYcgL=s96-c","dateLastActive":"2022-11-11T18:02:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-11-13T00:15:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3335934130,"url":"https:\/\/community.smartsheet.com\/discussion\/97523\/counting-cells-that-are-not-blank","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97523\/counting-cells-that-are-not-blank","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-11-09T13:58:22+00:00","dateAnswered":"2022-11-09T09:14:52+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":97529,"type":"question","name":"Automations & Indents","excerpt":"Hi Team! I am wanting to create an automation that once a form has been submitted, depending on the form's chosen 'location', it will automatically move the row under that specific outdent? Sorry I am not sure if I have explained that well enough. Any help is appreciated :)","categoryID":322,"dateInserted":"2022-11-09T03:42:29+00:00","dateUpdated":null,"dateLastComment":"2022-11-09T09:12:59+00:00","insertUserID":152163,"insertUser":{"userID":152163,"name":"Alicia_PRB","url":"https:\/\/community.smartsheet.com\/profile\/Alicia_PRB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0W637DEFRRCY\/nKOR4G5XGS3DJ.JPG","dateLastActive":"2022-11-09T23:00:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-11-13T00:15:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":12,"score":null,"hot":3335951128,"url":"https:\/\/community.smartsheet.com\/discussion\/97529\/automations-indents","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97529\/automations-indents","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2022-11-09T09:12:59+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

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公式手册模板!

0.01, [% Complete]@row…","categoryID":322,"dateInserted":"2022-11-10T23:19:23+00:00","dateUpdated":"2022-11-10T23:35:16+00:00","dateLastComment":"2022-11-12T22:33:02+00:00","insertUserID":154851,"insertUser":{"userID":154851,"name":"Jodi C","url":"https:\/\/community.smartsheet.com\/profile\/Jodi%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5qlOufSjfDo!xBtOMYTHJno!KTQ5OLC8XX5","dateLastActive":"2022-11-12T22:30:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":154851,"lastUserID":154851,"lastUser":{"userID":154851,"name":"Jodi C","url":"https:\/\/community.smartsheet.com\/profile\/Jodi%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5qlOufSjfDo!xBtOMYTHJno!KTQ5OLC8XX5","dateLastActive":"2022-11-12T22:30:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":33,"score":null,"hot":3336415945,"url":"https:\/\/community.smartsheet.com\/discussion\/97635\/combining-if-and-if-and-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97635\/combining-if-and-if-and-formulas","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":97635,"commentID":350976,"name":"Re: Combining IF and IF AND Formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/350976#Comment_350976","dateInserted":"2022-11-12T22:33:02+00:00","insertUserID":154851,"insertUser":{"userID":154851,"name":"Jodi C","url":"https:\/\/community.smartsheet.com\/profile\/Jodi%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5qlOufSjfDo!xBtOMYTHJno!KTQ5OLC8XX5","dateLastActive":"2022-11-12T22:30:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-11-12T22:32:44+00:00","dateAnswered":"2022-11-11T15:41:39+00:00","acceptedAnswers":[{"commentID":350857,"body":"

Hi @Jodi C<\/a>,<\/p>

I automated my Status column the same way using this.<\/p>

=IF([% Complete]@row = 0, \"Not Started\", IF(AND([% Complete]@row >= 0.01, [% Complete]@row < 1), \"In Progress\", \"Complete\"))<\/p>

Hope this helps!<\/p>

BRgds,<\/p>

@Chadd<\/a> <\/p>

Some times when making changes to a sheet, writing formulas does it a lot, it will look like a row is blank but it is not. Then a form or a move\/copy row will add rows with blank rows above them. To fix this, simply highlight the first empty row and then shift-select the final row at the bottom of the sheet. Then delete them all. Then when you refresh the sheet it will add about 10 blank rows at the bottom.<\/p>

That should stop the blank rows you're getting. At least until you do something that accidentally adds blank rows again like writing another formula without deleting the row you wrote it in.<\/p>"},{"commentID":350912,"body":"

Got it - Thank you!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-11-11 20:11:40","updateUser":{"userID":154725,"name":"Chadd","url":"https:\/\/community.smartsheet.com\/profile\/Chadd","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-11T20:10:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":319,"urlcode":"functionality","name":"functionality"}]},{"discussionID":97661,"type":"question","name":"is there an easier way to format a cell to read blank if value is 0?","excerpt":"i have a large report with many countifs to calculated data over time. I want to change it so that instead of displaying the 0 when the formula calculates a 0 the cell is just left blank. I've been using If statements but the formula is so long i wonder if there is an easier way. for example I might use…","categoryID":322,"dateInserted":"2022-11-11T19:02:41+00:00","dateUpdated":null,"dateLastComment":"2022-11-11T20:08:09+00:00","insertUserID":154873,"insertUser":{"userID":154873,"name":"Angela B","url":"https:\/\/community.smartsheet.com\/profile\/Angela%20B","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-11T21:22:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2022-11-12T17:23:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":16,"score":null,"hot":3336391250,"url":"https:\/\/community.smartsheet.com\/discussion\/97661\/is-there-an-easier-way-to-format-a-cell-to-read-blank-if-value-is-0","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97661\/is-there-an-easier-way-to-format-a-cell-to-read-blank-if-value-is-0","format":"Rich","lastPost":{"discussionID":97661,"commentID":350910,"name":"Re: is there an easier way to format a cell to read blank if value is 0?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/350910#Comment_350910","dateInserted":"2022-11-11T20:08:09+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2022-11-12T17:23:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-11-11T20:32:13+00:00","dateAnswered":"2022-11-11T20:08:09+00:00","acceptedAnswers":[{"commentID":350910,"body":"

That's really the only way to do it. You can copy\/paste the COUNTIFS portion though to avoid having to type it out twice.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-11-11 20:32:13","updateUser":{"userID":154873,"name":"Angela B","url":"https:\/\/community.smartsheet.com\/profile\/Angela%20B","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-11T21:22:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">