COUNTIFS based on Children criteria of certain row

Sandy Glassberg

Context: Please see screenshot attached. Project Manager "Fred" has 2 "active" projects in January: Project 1 and Project 2, which are Children under "Fred". This count of 2 is determined by some percentage of the project being completed.

Goal: Count the number of active projects per month based on % complete being greater than 0.

Request: Can someone please provide me with the appropriate COUNTIF formula based on these criteria? Thank you so much in advance!

Screen Shot 2020-01-16 at 11.01.26 AM.png

Sandy Glassberg

A colleague helped me figure it out! Here's the formula he came up with:

(using a hidden helper column called "PM")

=COUNTIFS(CHILDREN($PM$12), "Fred", CHILDREN([Jan 2020]12).

I was having issues at first because various rows were not indented properly and created circular references. But this appears to work fine! If anyone has better suggestions, I'm all ears.

Andrée Starå

Hi Sandy,

Excellent!

Thanks for sharing!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Please help the Community by marking the post that helped answer your question or solve your problem with the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Sandy Glassberg

I believe part of your formula may be missing. You have the range but not the criteria for your second set. Could you please post the full formula?

Additionally... Please don't forget to mark the most appropriate response as being helpful. This will mark your post as having an "Accepted Answer" and will let others having a similar issue know that a solution can be found.