Counting completed items, only if relevant

I have rows of items. Each row starts with 2 columns - Done and Not relevant.

When someone actions a row, they tick off the checkbox under "Done".

If the row is not applicable, they tick off the checkbox under "Not relevant".

The rows are children of a Header row which counts the rows completed using the formula ="Completed: " + COUNTIF(CHILDREN(Done7), 1) + " of " + ((COUNTIF(CHILDREN(Done7), 1) + (COUNTIF(CHILDREN(Done7), 0))))

I want to exclude from this count the rows that are ticked as "Not relevant"

Is this possible?

Find more posts tagged with

Accepted answers

David Tutwiler

Sorry, something is going weird with the Post interface and it wouldn't let me edit. The correct formula should be this (the first Not relevant is missing the 7):

="Completed: " + COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7),0) + " of " + ((COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7), 0)) + (COUNTIFS(CHILDREN(Done7), 0, CHILDREN([Not relevant]7),0)))))

All comments

David Tutwiler

I think you would just need to make your COUNTIF into COUNTIFS. It would look like:

="Completed: " + COUNTIFS(CHILDREN(Done@row), 1, CHILDREN([Not relevant]),0) + " of " + ((COUNTIFS(CHILDREN(Done7), 1, CHILDREN([Not relevant]7), 0)) + (COUNTIFS(CHILDREN(Done7), 0, CHILDREN([Not relevant]7),0)))))

David Tutwiler

Sorry, something is going weird with the Post interface and it wouldn't let me edit. The correct formula should be this (the first Not relevant is missing the 7):

Neil Watson

Thanks David, much appreciated

David Tutwiler

No problem, glad it works.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.