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

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

Paul Newcome

Try something like this...

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

All comments

Paul Newcome

Try something like this...

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

Gunn_Jack

@Paul Newcome Perfect, thank you!

Paul Newcome

Happy to help. 👍️

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?

Gunn_Jack

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.