COUNTIF with children
EDIT: I kind of solved my issue by using a helper column in which I typed "strenghts" or "risks" depending on which parent row the child row was under. Then I used the CONTAINS function:
=COUNTIFS(Category:Category, CONTAINS("HR", @cell), helper:helper, "strengths")
This gave me the correct count. But the helper column will still need to be updated manually as rows are added.
ORIGINAL QUESTION: I have a sheet separated into two groups for STRENGTHS and RISKS using parent/child hierarchies.
Using a sheet summary, I want to count how many Strengths are categorized as Development, IT, HR, etc (using a "Category" dropdown column). Then I want to do the same for Risks.
The idea is a count of Category = Development IF it's a child row of the STRENGTHS row.
Does that make sense? Can someone help me get started?
Best Answer
-
Use the formula below in the helper column and make it a column formula so it doesn't require manual updating.
=PARENT(Evaluation@row)
Right-click on the cell and convert it to a column formula.
Answers
-
Use the formula below in the helper column and make it a column formula so it doesn't require manual updating.
=PARENT(Evaluation@row)
Right-click on the cell and convert it to a column formula.
-
Thank you @Devin Lee !! I learned something new today. That formula did the trick for making sure new rows get the helper column updated manually.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives