Count Raised flags under child and grandchildren

rstefanis41586

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!

 

 

 

 

Ofir Marco

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.

 

 

rstefanis41586

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!

Ofir Marco

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

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

 

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

Ofir Marco

Can you explain the logic of your solution?

L_123

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.

BeckyK

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

 

Thanks!