RYG Milestone AND All Other Tasks

Chad Dixon

Hello -

I have a team project plan that consists of 1) milestone tasks, and 2) all other tasks. There is a column ("Milestone') that is a checkbox that denotes if it is a milestone task if checked.

At the bottom of the Smartsheet, I want to have a milestone summary that reports the following:

Milestone task - If at least, one milestone task is red, then red. If at least one milestone task is yellow but with no red, then yellow. If all green, then green.

Beneath that section, I also want to report something similar but looking at all other tasks (non-milestone).

If at least, one milestone task is red, then red. If at least one milestone task is yellow but with no red, then yellow. If all green, then green.

Any idea how I can achieve this?

Thanks!

Chad

Paul Newcome

Can you provide a screenshot of how your sheet is laid out along with how you want your summary laid out? Sensitive/confidential data can be removed, blocked, and/or replaced with "dummy data" as needed.

Chad Dixon

Hi Paul -

Sure - please see below. I have about ~35 project plans just like this. My end goal is to roll up a summary of each milestone and non-milestone tasks, and display it on a dashboard.

Thanks!

Paul Newcome

Do you have access to Sheet Summary Fields? If not, would you be open to adding in another row at the top of the sheet and indenting the project plan rows (not the summary rows) one additional level under that?

Chad Dixon

I do have access to sheet summary fields, as I am a licensed user. Can you share your thoughts in what you're thinking?

Paul Newcome

Your current setup will end up throwing #CIRCULAR REFERENCE errors in each of the summary cells. If you move those to Sheet Summary Fields, you will be able to run those metrics without having to worry about the error.

From there the formulas should be pretty straightforward.

Milestone Status:

=IF(COUNTIFS(Milestone:Milestone, 1, [Task Status]:[Task Status], "Red") > 0, "Red", IF(COUNTIFS(Milestone:Milestone, 1, [Task Status]:[Task Status], "Yellow") > 0, "Yellow", "Green"))

# of Outstanding Milestones:

=COUNTIFS(Milestone:Milestone, 1, Complete:Complete, <> 1)

Non-Milestone Status:

=IF(COUNTIFS(Milestone:Milestone, <> 1, [Task Status]:[Task Status], "Red") > 0, "Red", IF(COUNTIFS(Milestone:Milestone, <> 1, [Task Status]:[Task Status], "Yellow") > 0, "Yellow", "Green"))

# of Outstanding Non-Milestones:

=COUNTIFS(Milestone:Milestone, <> 1, Complete:Complete, <> 1)

Chad Dixon

Wow - this looks good! Clean and happy to get rid of that CIRCULAR REFERENCE error.

Quick question - how can I get the count-if to only report milestones/non-milestones that are only past due (="red")?

So I am looking for two additional formulas that would provide # of milestones that are past due and the same for non-milestones.

Also, I am sure I can do some pretty cool things once I set up on these summary fields for all project plans, such as roll it into a chart/graph of some sort on a dashboard and filter on various scenarios?

Thanks again - you were a huge help on this!

Paul Newcome

To incorporate "Red", it would go something like this...

=COUNTIFS(Milestone:Milestone, 1, Complete:Complete, <> 1, [Task Status]:[Task Status], "Red")

Chad Dixon

Thanks, Paul!

Do you know how I can link the data from the summary fields to a dashboard? For example, once I have created all project plans, which will include the same summary fields, I am trying to list those four summary fields to a sheet that provides a high-level summary for each client. Here's a screenshot.

Under my previous set-up, I could "link" to the cell from the other sheet. There may be a better way to do this then my current set up, so any suggestions you have would be much appreciated.

Thanks again!