Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Function for Counting Children of Parent Rows

    Employee
    in Formulas and Functions
    This discussion was created from comments split from: Function for Counting Children of Parent Rows.
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭

      Morning @Kelly Moore i have read this thread and was wondering whether you could support these related queries.

      1. I am trying to capture the number of children that have a 'Red' RAG from another sheet. I have tried

      =COUNTIFS(CHILDREN([{P1 Health}]), "Red") - with no luck.

      2. I would then like to see how I could apply this formula to another data set and find all Children with a 'Red' RAG and 'Governance' in a column.

      Any help would be appreciated.

      Thanks
      James

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @JamesQ4 - Not knowing your sheets makes it impossible to give a specific answer, but I suspect you are going to need to incorporate a collect() function or a helper column to make this work.

      Countifs presumes that all of your criteria exist on a specific row, so if you are trying to split sheets it isn't going to work. Using collect() allows you to create a hypothetical set of data that meets specific conditions, then do work on those conditions - so, for example, you could collect all tasks names that are red on the other sheet, then count those task names that match the names of the children cells you are looking at.

      Alternatively, just pull everything onto the same sheet by adding a helper column that pulls your RAG from the other sheet onto this one, then you can use countifs.

      · Share on FacebookShare on Twitter
    • Community Champion

      Hey @JamesQ4

      Unfortunately, children cannot be directly used as cross sheet ranges. The work around is to add a helper column to your source sheet that can be used to find the child rows. You can do this a couple different ways, depending upon the layout of your sheet. If you could provide a screenshot of your source sheet (no sensitive data), I can assist with your helper column. Specifically I'm interested in how many 'levels' of hierarchy you have. Regular rows vs parent/child, Grandparent rows, etc.

      Kelly

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Thank you @Kelly Moore & @Jgorsich

      · Share on FacebookShare on Twitter
    • Community Champion

      @JamesQ4

      Let me know if you have any questions with your helper column.

      Kelly

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions