Stumped on a formula: multiple criteria COUNTIF

SGrese

I need a COUNTIF formula pulls results for a particular status result (i.e. Complete, In Progress, etc.) for all items one level down the parent/child heirarchy from a referenced parent cell.

My team has multiple projects running in parallel that need to report out their progress. I was able to give a simple update for all projects, but breaking them down into their buckets is proving more challenging. My Sheet Summery has formulas like the below for the overall status reporting:

=COUNTIF(Status:Status, "COMPLETE")

I now want that same result, but only for the projects housed under each initiative bucket (i.e. AEM: FOUNDATIONAL, TECH STACK: FOUNDATIONAL, ETC.). I have included snapshots as reference.

I thought I was close with the below but it is coming back with 0 instead of 3. One thing of note, the parent "AEM FOUNDATIONAL" is in cell Primary112 for this example:

=COUNTIF(CHILDREN(Primary112), Status:Status = "Complete")

Thanks for any help you can give!

Amber Eakin

Hi, @SGrese ! I'd recommend a Helper column. You could call it "Parent" and use this column formula for it: =PARENT(Primary@row)

I use this quite a bit for reporting purposes. You could hide it so that it doesn't take up space on your sheet if you want. Then, your formula would be =COUNTIFS(Parent:Parent, "AEM FOUNDATIONAL", Status:Status, "Complete")

You could use this same formula and update the "Parent" phrase as the condition for each of your larger categories.

I hope that helps!