Excluding child rows from counts in formulas in a sheet

Janet Krieger

I have inherited a project with a metrics sheet that is the source for charts in a Dashboard. The metircs sheet is using formulas to CountIf cretain criteria are met, such as Status, Priority, Owner, etc. I would like these countif formulas to only be counting the Parent rows and not include the child (indented) rows. For example, the current formula to count the number of Rows with a Status of "Backlog" is this" =COUNTIFS({People Team Initiatives Range 3}, "Backlog", {People Team Initiatives Range 1}, "<>") but this counts every row with this status. How can I only count the parent rows and exclude child rows? Thanks!

Andrée Starå

Hi @Janet Krieger

I hope you're well and safe!

You'd need to add a so-called helper column to the source sheet.

Something like.

I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

You can then use that in the formula to determine if it's a parent or not.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Janet Krieger

I hope you're well and safe!

You'd need to add a so-called helper column to the source sheet.

Something like.

I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)

You can then use that in the formula to determine if it's a parent or not.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Janet Krieger

Thank you. That does help. I have created a helper column and have beenable to get the data to display appropriatley.

Andrée Starå

@Janet Krieger

Excellent!

You're more than welcome!