Return count of parent rows that contain specific data in child row

Hi all - this might be an easy one but I'm blanking. Here's a table:

The "2" being returned in row 13 uses: =COUNTIF([Primary Column]3:[Column2]11, "Green"). Easy enough.

I'm having trouble with rows 14 and 15. I'd like those to return a count of categories (i.e. parent rows) that contain a child with specific data. So, row 14 (green foods) should return a count of 1 since there is only one parent with one or more green foods. Row 15 (red foods) should return a count of 2 since there are two parents with children that have the color red.

Is there a simple function I'm missing here?

Thank you!!

Find more posts tagged with

Accepted answers

Itai Perez

Hey @browns99df,

I have an idea that is far from ideal but maybe it will help you.

Add a helper column with a formula that counts the descendants that are in your criteria, then COUNTIF this column without counting the '0', <>0.

Helper formula: =COUNTIF(DESCENDANTS([Column 2]1), "Green")

Main formula: =COUNTIF(Helper1:Helper9, <>0)

I am assuming other members will have better ideas for you but this works :)

All comments

Itai Perez

Hey @browns99df,

I have an idea that is far from ideal but maybe it will help you.

Add a helper column with a formula that counts the descendants that are in your criteria, then COUNTIF this column without counting the '0', <>0.

Helper formula: =COUNTIF(DESCENDANTS([Column 2]1), "Green")

Main formula: =COUNTIF(Helper1:Helper9, <>0)

I am assuming other members will have better ideas for you but this 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.