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.

COUNTIF on FLAGS at PARENTS level not returning any value (number)
Hi Smart Community, I am trying to Sum my CHILDREN() Countif but it is not giving any value. All my sub-children is indicated as flag (1 or 0) Example When there is a delay release in a category Bug Fix : it will flagged out RED. On Bug Fix, I did =COUNTIF(CHILDREN()),1)+"" This seems to work fine and return with 2 (which…
Sumifs with range
Hi, Refer to below, trying to get total sum of certain items. Scenario : Sum numbers from the column, Color, if the column, Fruit is "Apple". I tried below, but returning error. Please help. =SUMIFS([color]99:[color]104), fruit99:fruit104, "Apple")
Circular Reference
Smartsheet Community How do I SUM this formula with in the Same Row without Receiving a Blocked or Circular Reference? Row17: IFERROR(Passed17 / $[Total Test Cases]$17, 0) Row 18:Is a Sum within the same Column as Row 17 =SUM(Passed18:Deferred18) Thanks
Updating a Date Column after another cell is updated.
I am trying to have a Date Column update with today's date when a single select option is chosen. I don't want to use the system update date column because that will get updated whenever any cell is selected. For example: I have a single selection column with several options: Estimating Complete, Quote Sent, and Sale. I…
Use Wildcards in an INDEX MATCH formula
Hi everyone, I need to use INDEX MATCH formula to get a DS# with a CNDT# in another report. The easy formula would be: =INDEX({DS Report Range 2}, MATCH([CNDT Number]1, {DS Report Range 3}, 0)), "") but the thing is that sometimes there are more than one CNDT#'s in a single cell. Since INDEX MATCH looks for the exact match…
RYGB Symbols Average Formula
I currently having issues with creating formula that gets the average of 4 cells that have the option of 4 different symbols (Red,Yellow,Green,Blue). The goal is to show the average of the four cell symbol's in one here is what I have so far. =IF(COUNTIF([PW Goal]3, [BB Goal]3, [JQ Goal]3, [MC Goal]3, "Red")) = 4, "Red",…
Survey Form(S)
I'm currently working on Survey Form. I have made one for one "X" department. I need to make another survey for for "Y" department. The two departments have different heading but the two have same questionnaire but different employee names. How do I clone the first form into the second. And is it possible to the results…
Match and Index functions to create a table referencing another sheet
Hello, I need help creating a formula which will work. Sheet 1 - contains my data Sheet 2 - Contains the tracking matrix I am trying to create I need a formula which will look at sheet 1, determine if an inspection has taken place within a certain month, and then return the date of that inspection into sheet 2. I've been…
Formula If & AND referencing form another sheet
Is there a formula for the following scenario referencing from another sheet? if "checkbox = ticked under Unique column" AND "Fruit = Apple" Then display [color]@row Refer to below, result I want to get is "Green" =IF(AND({SheetA Range 3}=1, {SheetA Range 2}=Apple), {SheetA Range 4}) I tried with above formula, but didnt…
Incorrect Argument Set: Nested IF Formula with 3 Logic Statements
I have the following formula that works and I would like to add one more criteria, if Bid? = "Bid" then return 100% in %Complete Column This is the original working formula: =IF([Include in Estimate]@row = 0, 1, IF(OR(NOT(ISBLANK(Units@row)), NOT(ISBLANK([Fixed Cost]@row))), 0.25, 0)) This is my edit which returns the…

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

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

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?<\/p>

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\"), \"Untrained\", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\")), \"In Progress\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"In Process\") >= 1, \"In Process\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Trained\") >= 1, \"Trained\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Proficient\") >= 1, \"Proficient\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Preceptor\") >= 1, \"Preceptor\")))))))<\/p>

Please let me know what we need to tweak<\/p>

Kelly<\/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":107653,"type":"question","name":"Sum all children rows under parent","excerpt":"I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to the quarters which sum up to the year? I only want them to sum at one indent in from the immediate parent row.","snippet":"I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to…","categoryID":322,"dateInserted":"2023-07-16T12:23:14+00:00","dateUpdated":"2023-07-17T08:25:09+00:00","dateLastComment":"2023-07-17T14:32:04+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T13:40:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":163321,"lastUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T13:40:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":37,"score":1,"hot":3379117218,"url":"https:\/\/community.smartsheet.com\/discussion\/107653\/sum-all-children-rows-under-parent","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107653\/sum-all-children-rows-under-parent","format":"Rich","lastPost":{"discussionID":107653,"commentID":385551,"name":"Re: Sum all children rows under parent","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385551#Comment_385551","dateInserted":"2023-07-17T14:32:04+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T13:40:29+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-17T11:09:14+00:00","dateAnswered":"2023-07-17T09:37:31+00:00","acceptedAnswers":[{"commentID":385514,"body":"

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

I hope you're well and safe!<\/p>

You can add a so-called helper column that indicates a level and then use that in the calculation.<\/p>

Try something like this for the helper column. (change the column names as needed)<\/p>

I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)<\/p>

Would that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

@lea.s6<\/a> when a page is loaded in WorkApps the setting related to how the page shows on a mobile can be amended here:<\/p>

\n
\n \n $\"image.png\"$ <\/img><\/a>\n <\/div>\n<\/div>\n

<\/p>"},{"commentID":385520,"body":"
Thanks for your answer !<\/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":[]}">

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags