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

Tags:

• ✭✭✭✭✭✭

Try something like this...

=IF(COUNTIFS(CHILDREN(), "Inconsistent") >0, "Inconsistent", "Competent")

• ✭✭✭✭✭✭

Try something like this...

=IF(COUNTIFS(CHILDREN(), "Inconsistent") >0, "Inconsistent", "Competent")

• ✭✭✭✭

@Paul Newcome Perfect, thank you!

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭

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

• ✭✭✭✭✭✭

Do you have a column that will have data in every row?

• ✭✭✭✭

Hi @Paul Newcome really appreciate your help but i think i cracked it:

=IF(COUNTIFS(CHILDREN(), "") > 0, "", IF(COUNTIFS(CHILDREN(), "Inconsistent") > 0, "Inconsistent", "Competent"))

:)

Thanks,

Jack

• ✭✭✭✭✭✭

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"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!