CountIFS for Parent Row Only

mmurray109161 · 10/28/19

你好,

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 · 10/28/19

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å · 10/29/19

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 · 05/02/23

@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?

CountIFS for Parent Row Only

Comments

Help Article Resources

Categories