Count Parent rows and rows without children

Filip

Hello,

We have an archive sheet where all completed projects are automatically migrated via "move row" workflow.

Some projects have multiple child rows while some do not have any.

I would need a formula to count a total number of completed projects regardless if they have or do not have child rows. If they have child rows, they only need to be counted once.

In the example below, the total count of completed projects would be 5.

Is there a formula that can count this?

Thanks

Andrée Starå

Hi @Filip

I hope you're well and safe!

Could you use the Category column for the count?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

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!

Filip

Hi Andre,

My apologies, in our project sheet all sub-tasks also have the corresponding category selected, please see below. Sometimes IT category project might have a subtask for HR so we keep category column updated for all child and parent rows.

Is there any other way to count completed projects?

Thanks,

Filip

Andrée Starå

@Filip

No worries!

You could use something like this and reference that in a COUNTIF formula.

=IF(COUNT(CHILDREN([Project Name]@row)) > 0, COUNT(ANCESTORS())

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.

Make sense?