# Count Raised flags under child and grandchildren

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!

Tags:

• 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.

• 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

• ✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!