COUNTIFS PARENT cross reference

In my summary sheet, I need to cross reference the PARENT rows in my data sheet (status) to count the number of open non-conformities (with less than 100% complete). Basically, the counting in summary sheet needs to take into consideration two conditions in the status sheet:

Be a PARENT row, and
% complete <1

I have tried everything I can and I still cannot it to work

Find more posts tagged with

Formulas

Project Management

Accepted answers

Ramzi K

@Misayely Abias

Add a "helper" column to your status sheet called IsParent (of type Checkbox) and then put a column formula in it:

=IF(COUNT(CHILDREN(Item@row)) > 0, true)

In your summary sheet use the formlua =COUNTIFS({Status Sheet IsParent Column}, true, {Status Sheet % Complete Column}, <1)

I hope this help.

Cheers,

Ramzi

All comments

Ramzi K

@Misayely Abias

Add a "helper" column to your status sheet called IsParent (of type Checkbox) and then put a column formula in it:

=IF(COUNT(CHILDREN(Item@row)) > 0, true)

In your summary sheet use the formlua =COUNTIFS({Status Sheet IsParent Column}, true, {Status Sheet % Complete Column}, <1)

I hope this help.

Cheers,

Ramzi

Misayely Abias

Ranzi, this is just perfect! 👌

Thank you so much

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.