Count Raised flags under child and grandchildren

03/16/18 Edited 12/09/19

Hello everyone,

I am trying to wrap my head around this one and I'm sure others have too.

 

I want to count all the Flags that are raised under a grandfather (Administration) : the children and their grandchildren.

Currently my solution works, but it's weak. It depends on a fixed range, and as time goes on, new grandchildren and children are added. If the formula's range is not updated, important flags can go unreported.

Here's the formula:

=COUNTIF(Flag3:Flag27, 1) + " Flag(s) raised"

 

If I could somehow make this work by including the COUNT(CHILDREN()) formula it'd be nice but I can't wrap my head around it. Any feedback would be appreciated.

 

 

Thanks!

 

 

 

 

SS.png

Popular Tags:

Comments

  • I am also struggling counting with "Children" column based on criteria.

    So i have done something similar using another calculation column ([Flag status]).

    here the formula for the extra column:  =IF(Flag2 = 1, 1, 0)

    Here is the formula i used for the [Count flags] column:

    =SUM(CHILDREN([Flag status]1))

    It sums the additional column by children so that only when there is a parent it will sum the values.

     

     

    Flag count.png

  • Hello Ofir,

     

    Thanks for your insight. I was considering something like you, where I would "roll up" the sum of tasks (grandchildren) into children then sum children into the original parent. 

     

    I'm going to give your solution a try, but I hope in the future Smartsheets developers will figure out a way to do this in a simple formula.

     

    Thanks!

  • I hope so as well.

    There are a few things that i wish could have been improved, this is one of them.

    Meanwhile we can only think of some workarounds. 

    at least it keeps our minds sharp :)

  • L@123[email protected] ✭✭✭✭✭

    https://app.smartsheet.com/b/publish?EQBCT=cb5ea5d88ab14885abd7af9fd807d511

     

    I might have a solution. Let me know if this is what you were thinking of

  • Can you explain the logic of your solution?

  • L@123[email protected] ✭✭✭✭✭

    The flag is raised at the parent column if any of the children or grandchildren have a flag so you cannot miss any flags through the use of an if statement and a countif. The numbers on the right shouldn't be much different than yours, i just added an if statement to make them blank when not reporting a parent cell.

  • Can you share your sheet again?  I'm also looking for a way to count flags at the parent level.

     

    Thanks!

Sign In or Register to comment.