Sheet Summary: COUNTIFS formula to count up completed tasks

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.

Best Answer

Answers

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!