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!!
Best Answer
-
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 :)
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
Answers
-
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 :)
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!