CountIFS for Parent Row Only

mmurray109161

Hello - 

I am displaying some information regarding things that are approved, in queue or denied. But I am needing to only count parent rows and not the children for them.  So the data collected are just for the overall brand and not sub-brands. Is there a way for me to make this happen?

Thank you - 

Mmurray 

Frank Falco

You will need to add some columns that you can hide to calculate which rows are a parent.

If you only want to absolute top-level parent, you can use

Level: =COUNT(ANCESTORS()+ 1

When Level = 1 it is the top-level, 2, 3, and so on for sub levels

If you want to know about sub parents

Subs: =COUNT(CHILDREN()) 

any row that has Subs>0 is also a parent

Using these two you can get a good knowledge of where you are in the hierarchy.

 

When you have this in place you can then use the level or subs values in your COUNTIFS formulas. 

Andrée Starå

Hi Mmurray,

I use this one all the time in my client solutions. I name the column Level and use that to simplify conditional formatting and more.

Try something like this.

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

The same version but with the below changes for your and others convenience.

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

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

NikkiOno

@Andrée Starå this counts the parent rows and give them a 1 but how do i get a 2, 3 for children and grandchildren rows?