If all = Competent then competent, if not, then inconsistent
Hi all,
I need help with a formula. People choose their skill based on a certain topic:
- Competent
- Inconsistent
- Expert
I need a formula that looks at all the children and determines that:
- If 1 inconsistent, no matter what else, show as inconsistent
- If all competent or expert, show as competent
I am guessing it can be easily achieved by saying if there is 1 inconsistent then inconsistent, otherwise, show competent. But i am not sure how this will look in a formula?
Any help would be much appreciated.
Thanks,
Jack
Best Answer
-
Paul Newcome ✭✭✭✭✭✭Try something like this...
=IF(COUNTIFS(CHILDREN(), "Inconsistent") >0, "Inconsistent", "Competent")
10xViz
Answers
-
Paul Newcome ✭✭✭✭✭✭
Try something like this...
=IF(COUNTIFS(CHILDREN(), "Inconsistent") >0, "Inconsistent", "Competent")
10xViz
-
Gunn_Jack ✭✭✭✭
@Paul NewcomePerfect, thank you!
-
Paul Newcome ✭✭✭✭✭✭
-
Gunn_Jack ✭✭✭✭
Hi@Paul Newcome- sorry to bother you but just as an addition, if everything is blank, how do i get the parent row to show inconsistent? Not an issue if too difficult, as i can just pre-populate to inconsistent.
Cheers,
Jack
-
Paul Newcome ✭✭✭✭✭✭
Do you have a column that will have data in every row?
10xViz
-
Gunn_Jack ✭✭✭✭
Hi@Paul Newcomereally appreciate your help but i think i cracked it:
=IF(COUNTIFS(CHILDREN(), "") > 0, "", IF(COUNTIFS(CHILDREN(), "Inconsistent") > 0, "Inconsistent", "Competent"))
:)
Thanks,
Jack
-
Paul Newcome ✭✭✭✭✭✭
Happy to help.️
Your formula currently will display a blank if there is even one child row that is blank. So if all are "Expert" except for one blank, it will show as blank. To write it so that it is only blank if ALL child rows are blank, you are going to want something more along the lines of...
=IF(COUNTIFS(CHILDREN(), "")= COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "", IF(COUNTIFS(CHILDREN(), "Inconsistent") > 0, "Inconsistent", "Competent"))
10xViz
Help Article Resources
Categories
=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N\/A]@row = 1), 1, 0), AVG(CHILDREN()))<\/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":108195,"type":"question","name":"COUNTIF Function Not Working Correctly","excerpt":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], \" \")","snippet":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this…","categoryID":322,"dateInserted":"2023-07-27T16:41:32+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164192,"lastUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3380962360,"url":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","format":"Rich","lastPost":{"discussionID":108195,"commentID":387638,"name":"Re: COUNTIF Function Not Working Correctly","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387638#Comment_387638","dateInserted":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+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-27T19:10:56+00:00","dateAnswered":"2023-07-27T16:47:29+00:00","acceptedAnswers":[{"commentID":387588,"body":"
