Formulas and Functions

I think this should work:<\/p>

=IF(COUNTIF(Referencia:Referencia, Referencia@row) > 1, 1)<\/p>

Have a great day.<\/p>

Matt<\/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":107890,"type":"question","name":"Need Help with Formula to Roll Up and Average Schedule Health.","excerpt":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am trying to roll up\/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall…","snippet":"Need Help with Formula to Roll Up and Average Schedule Health. I have one that now updates the Schedule Health RYG based on the Start Date\/End Date and Task Status. Next, I am…","categoryID":322,"dateInserted":"2023-07-21T00:56:52+00:00","dateUpdated":null,"dateLastComment":"2023-07-23T11:39:03+00:00","insertUserID":163738,"insertUser":{"userID":163738,"name":"BArmendariz","url":"https:\/\/community.smartsheet.com\/profile\/BArmendariz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcJ5xYSxEGHhOSSWfKvut9ttlA26d6GuoryTax_h13HbQ=s96-c","dateLastActive":"2023-07-21T16:10:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163540,"lastUser":{"userID":163540,"name":"stevediaz","title":"mr","url":"https:\/\/community.smartsheet.com\/profile\/stevediaz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfxw60LesP__pWW-huKx7XxMBqFzxmYEVfP7e4_MbE-=s96-c","dateLastActive":"2023-07-23T11:39:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":49,"score":null,"hot":3380018755,"url":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107890\/need-help-with-formula-to-roll-up-and-average-schedule-health","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107890,"commentID":386551,"name":"Re: Need Help with Formula to Roll Up and Average Schedule Health.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386551#Comment_386551","dateInserted":"2023-07-23T11:39:03+00:00","insertUserID":163540,"insertUser":{"userID":163540,"name":"stevediaz","title":"mr","url":"https:\/\/community.smartsheet.com\/profile\/stevediaz","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfxw60LesP__pWW-huKx7XxMBqFzxmYEVfP7e4_MbE-=s96-c","dateLastActive":"2023-07-23T11:39: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-21T16:11:26+00:00","dateAnswered":"2023-07-21T12:51:20+00:00","acceptedAnswers":[{"commentID":386383,"body":"

@BArmendariz<\/strong><\/a><\/p>

You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.<\/p>

You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)<\/p>

Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)<\/p>

If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.<\/p>

For example:<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), \"Blue\") = COUNT(CHILDREN()), \"Blue\", IF(COUNTIF(CHILDREN(), \"Green\") = COUNT(CHILDREN()), \"Green\", IF(COUNTIF(CHILDREN(), \"Red\") > 0, \"Red\", \"Yellow\"))), IF([% Complete]@row = 1, \"Green\", IF([End Date]@row < TODAY(), \"Red\", IF([% Complete]@row = 0, \"Blue\", \"Yellow\"))))<\/p>

<\/p>

The first half of this is dealing with Parent Rows:<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), \"Blue\") = COUNT(CHILDREN()), \"Blue\", IF(COUNTIF(CHILDREN(), \"Green\") = COUNT(CHILDREN()), \"Green\", IF(COUNTIF(CHILDREN(), \"Red\") > 0, \"Red\", \"Yellow\")))<\/p>

The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.<\/p>

The second half is child rows:<\/p>

, IF([% Complete]@row = 1, \"Green\", IF([End Date]@row < TODAY(), \"Red\", IF([% Complete]@row = 0, \"Blue\", \"Yellow\"))))<\/p>

If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.<\/p>

I hope this helps...<\/p>

Kind regards<\/p>

Debbie<\/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":107888,"type":"question","name":"Filter distinct values only into new column","excerpt":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only give me the unique values of [(Cells Linked from TE Tracker)] From what I've seen, there is no direct function that can do this, but has anyone…","snippet":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only…","categoryID":322,"dateInserted":"2023-07-20T22:29:01+00:00","dateUpdated":null,"dateLastComment":"2023-07-22T20:06:16+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T20:06:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163466,"lastUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T20:06:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":42,"score":null,"hot":3379950317,"url":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","format":"Rich","lastPost":{"discussionID":107888,"commentID":386542,"name":"Re: Filter distinct values only into new column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386542#Comment_386542","dateInserted":"2023-07-22T20:06:16+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-22T20:06: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-22T05:31:23+00:00","dateAnswered":"2023-07-21T02:06:24+00:00","acceptedAnswers":[{"commentID":386312,"body":"

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

<\/p>

There's no direct way to do this. However, you can add a checkbox helper column to identify duplicates. You can use the formula =IF(COUNTIF([Cells Linked from TE Tracker]:[Cells Linked from TE Tracker], [Cells Linked from TE Tracker]@row) > 1, 1, 0). This will add a check in the duplicates column for all values which appear more than once. You can then add the Distinct column with the formula =IF([Duplicate]@row=0, [Cells Linked from TE Tracker]@row). The Distinct column will only have the distinct values listed.<\/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":[]}">