Counting how many items are on the critical path

Sean Corcoran

I know I can filter my task list and only show critical items and we are working on dashboards and I would like to count how many items are on the critical path, how many are done and what ones are past due or coming to their due date. I would like the formula to work from a reference sheet that I am building for my dashboards to use that looks at another task list.

Ben Goldblatt

Hi @Sean Corcoran,

You could definitely use cross-sheet COUNTIFS formulas for these calculations and hopefully soon we'll see the new Group and Summary features in Reports carry over to Dashboard widgets as well. For critical path tasks, what I would recommend is applying your critical path filter and then inserting a "Critical Path" checkbox column in the sheet and checking all of the boxes on the rows in the filtered view. These checked boxes can be used in your formula criteria.

 Source Sheet w/ Critical Path Filter

If I want to see a breakdown of the various Status values for "Not Started", "In Progress", and "Complete", I could set up a formula sheet using formulas like:

• =COUNTIFS({Critical Path}, 1, {Status}, "Not Started")
• =COUNTIFS({Critical Path}, 1, {Status}, "In Progress")
• =COUNTIFS({Critical Path}, 1, {Status}, "Complete")

Destination Sheet with formulas

You could also create a Report to show this breakdown, setting a filter of "Critical Path is checked", grouping by Status, and summarizing the data by Status counts.

You could absolutely add criteria to your formulas to reference the End Dates of tasks to check for anything past due or nearing projected completion dates and you could use similar filter criteria in the report. Again, for right now these grouped and summarized reports won't show in Report widgets on a dashboard but this is coming soon.

More information on cross-sheet formulas, filtering in reports, and report widgets on dashboards can be found in the following Help articles from the Learning Center:

• Cross-sheet formulas
• Create Filter Criteria to Control Data in Report Builder
• Report Widget

I hope this helps!

Thanks,

Ben

Ben Goldblatt

Hi @Sean Corcoran,

You could definitely use cross-sheet COUNTIFS formulas for these calculations and hopefully soon we'll see the new Group and Summary features in Reports carry over to Dashboard widgets as well. For critical path tasks, what I would recommend is applying your critical path filter and then inserting a "Critical Path" checkbox column in the sheet and checking all of the boxes on the rows in the filtered view. These checked boxes can be used in your formula criteria.

 Source Sheet w/ Critical Path Filter

If I want to see a breakdown of the various Status values for "Not Started", "In Progress", and "Complete", I could set up a formula sheet using formulas like:

• =COUNTIFS({Critical Path}, 1, {Status}, "Not Started")
• =COUNTIFS({Critical Path}, 1, {Status}, "In Progress")
• =COUNTIFS({Critical Path}, 1, {Status}, "Complete")

Destination Sheet with formulas

You could also create a Report to show this breakdown, setting a filter of "Critical Path is checked", grouping by Status, and summarizing the data by Status counts.

You could absolutely add criteria to your formulas to reference the End Dates of tasks to check for anything past due or nearing projected completion dates and you could use similar filter criteria in the report. Again, for right now these grouped and summarized reports won't show in Report widgets on a dashboard but this is coming soon.

More information on cross-sheet formulas, filtering in reports, and report widgets on dashboards can be found in the following Help articles from the Learning Center:

• Cross-sheet formulas
• Create Filter Criteria to Control Data in Report Builder
• Report Widget

I hope this helps!

Thanks,

Ben

Sean Corcoran

@Ben G thanks for the response, I am going to try and take a look at this today

Sean Corcoran

@Ben G I had a chance to look at this and it would work but adding a helper field that needs to be checked if an item is on a critical path is not really solution because the critical path could change and you would have to check an un-check items when you update the project plan. I do apricate the feed back and it seems that since Smartsheet is not all that smart it might be the only solution.