Stumped on a formula: multiple criteria COUNTIF
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!
Answers
-
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!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist | Process Improvement Enthusiast
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!