Sheet Summary: COUNTIFS formula to count up completed tasks

victoria.anzalone76406

I have a schedule sheet with nested task sections.

e.g., Parent Row is called "Copy Editing" and underneath that are children rows for 15 book chapters with Task Names like "Copy Editing - Chapter 1", "Copy Editing - Chapters 2-3", etc.

I want a Sheet Summary formula that counts how many chapter rows have a value of 100% in the "% Complete" column. This formula worked in another sheet where all the Task Name values were the same (no chapter info appended to the end of the name), but I'm looking for a way to achieve the same thing with something like "CONTAINS" or "FIND" "Copy Editing", or even better, something like CHILDREN of parent row named "Copy Editing".

=COUNTIFS([Task Name]:[Task Name], "Copy Editing", [% Complete]:[% Complete], =1)

NOTE: I'm NOT looking for any solutions that involve making helper columns, removing the chapter names from the Task Name, etc. Just a revised formula that counts up rows where task name contains the specified value.

Andrée Starå

@victoria.anzalone76406

Excellent!

You're more than welcome!

The simplest method would be to add a so-called helper column to either show the level of the rows or if it's a parent or not.

Something like this. (Helper column named Level)

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

So the new formula would look something like.

=COUNTIFS([Task Name]:[Task Name], CONTAINS("Copy Editing", @cell), 
[% Complete]:[% Complete], 1,Level:Level,"")

Did that work?

Andrée Starå

Hi @victoria.anzalone76406

Try something like this. 

This formula will look at both parents and children. If you only want to include the children, we'd need to add another criterion.

=COUNTIFS([Task Name]:[Task Name], CONTAINS("Copy Editing", @cell), [% Complete]:[% Complete], 1)

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

victoria.anzalone76406

Thank you @Andrée Starå . That's exactly what I was initially thinking. How would you recommend revising it to ignore the parent rows? I do have a "Header" column where parent rows are checked off so I could probably just add a condition based on that, but rather than relying on that, I'm wondering if there's a more straightforward approach with an actual formula expression to include children only/exclude parents.

victoria.anzalone76406

I think my ideal solution would be something like "If Task Name is "Copy Editing" and % Complete is 100%, then show "All", else [your supplied formula]. What would be the right syntax for that?

Andrée Starå

@victoria.anzalone76406

Excellent!

You're more than welcome!

The simplest method would be to add a so-called helper column to either show the level of the rows or if it's a parent or not.

Something like this. (Helper column named Level)

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

So the new formula would look something like.

=COUNTIFS([Task Name]:[Task Name], CONTAINS("Copy Editing", @cell), 
[% Complete]:[% Complete], 1,Level:Level,"")

Did that work?