COUNTIF with children

Kristina S Otten
Kristina S Otten ✭✭✭
edited 01/11/22 in Smartsheet Basics

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

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Use the formula below in the helper column and make it a column formula so it doesn't require manual updating.


    Right-click on the cell and convert it to a column formula.