COUNTIF with children

Kristina S Otten

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?

Devin Lee

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.

Devin Lee

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.

Kristina S Otten

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.