Is there a formula to calculate the overall state of a specific group of projects in a metadata?

melisaozdeniz

Hi Smartsheet Community,

I am trying to calculate the total number of green, yellow and red projects I have on a sheet based on different project groupings. My goal is to be able to have a chart in the dashboard that would report on the overall state of a group of projects based on their location or strategic initiative.

Any suggestions on what this formula would look like would be greatly appreciated.

Thank you!

Melisa Ozdeniz

Lidiya S.

Hello,

There is a great way to do this using the COUNTIFS formula! Your sheet would need to reference the project grouping on a row level. Example below:

=COUNTIFS(Group:Group, =[Group Name]@row, Status:Status, "Red")

=COUNTIFS({Metadata Column with the grouping name}, ="Grouping Name or point towards the cell with the group name", {Metadata Column with the project status}, "Project Status Color")

Then you can point your chart at the Group and the specific column status you want to display to look something like this:

@ me to discuss further!

Lidiya Shutaya

lidiya@ddbconsultants.ca

melisaozdeniz

@Lidiya Shutaya Thank you so much for your help!

Lidiya S.

@melisaozdeniz No problem! If this worked out for you please set my answer to accepted in case someone else has a similar question :)