Count Help, Don't Know Where To Start

KristiTRUSD

Smartsheet question greenwaste project.pdf

Hi Everyone,

I would like to do a project status summary. Each parent is a school site, and the children are the projects that need completion. In the drop-down menus next to the projects I change the project status to complete when done. I'd like to create a list of all the school sites included in this project that shows when all three items are complete, I'd like to include the data as a dashboard widget later on. On the dashboard, I'd like to show the schools that are complete vs the schools in progress. I'd like to create a formula that automatically updates the school site as 100% complete after all three projects have been completed. I'm having trouble visioning how to bring this to life. I'd love input from all of you Smartsheet pros!

KristiTRUSD

Would I be able to use an IF Function for this possibly?

Paul Newcome

I would suggest a hidden helper column with the following column formula:

=COUNT(ANCESTORS())

Then you can use this formula in the Parent rows of the Status column:

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))

The parent rows of the Completion Date column would have this:

=MAX(CHILDREN())

From there you have quite a few options on how to grab your counts for schools in each status. The option(s) you go with is going to be dependent on how exactly you want the data displayed on the dashboard.