Adapting an Existing Formula

TanyaMac

Hi, I would like to adjust the formula that comes with the Project Management Office Templates. The Smartsheets template comes with this formula in the "Portfolio Metrics" sheets in the Total column and on the In Progress row. =COUNT(COLLECT({Project ID}, {Project Status}, $Label@row)). I would like to change Project ID to Programme Group (I know how to do this) what I would also like to do is only for it to only select "Clinical Operation Service Transformation" data in that Programme Group. Can I do this and if so how? We have a large PMO with a number of subdivisions so I need to break the data out into groupings.

Thanks so much

florian.zbinden7

hello @TanyaMac

I think you should adjust the formula like on this sheet :

add "{Project Category}, [Category 1]$1)" at the end of your formula, or just "{Project Category}, "Clinical Operation Service Transformation")" if you want to hardcode the "Clinical Operation Service Transformation".

hope this helps,

Florian

TanyaMac

Thanks so much for getting back to me so quickly. That works for the row - 'Not Started' but how should I adapt it so that I can change the formula in Project Schedule Health so that it also pulls in the data from "Clinical Operation Service Transformation" data in that Programme Group?

florian.zbinden7

I am not sure to understand your ask.

Can you add a screenshot or elaborate a bit more ?

If you look at this other formula, which additional criteria do you need ?

TanyaMac

We have a large PMO and I am trying to create a Programme Group reporting folder. We have 18 workstreams with anything form 1 -20 projects each. These are split into 3 Programme Groups that they report into and then we have 1 Programme Portfolio which all the high-level info gets pulled into. I am trying to create one of the Programme Group meta data sheets and need the formula to only pull the projects from the workstreams that report into that Programme Group. At the moment the formula is pulling all the info from all the workstreams and projects. The formula you gave me has enabled me to get that info for Project Status but I can't replicate it for the Project schedule Health (Active) and (All-time). Can you help with that?

florian.zbinden7

I am trying to make sure I understand, so here is my understanding : this is the "database" :

it's a pivot table basically, I just made it manually with this formula:

But it's only for "Programme Group A".

If this is not what you explained, can you please share screenshots.

thanks and best regards,

Florian

florian.zbinden7

sorry my bad, I forgot to filter for "Programme Group A".

TanyaMac

Hi @florian.zbinden7

Thank you so much for trying to help me and not giving up when I am being unclear. I have taken some screen shots to try to explain what I am trying to do.

In the metrics sheet below I am trying to pull all the info for just the Clinical Operation Service Transformation projects. I have managed to do this in the top row (Number of projects) using the first formula you shared. However in the other ones I am still not pulling the correct data as it is pulling for all the Programme Groups.

The data I have trying to select by is on another sheet please see below under the Programme Group column. For the above metrics I am trying to select only the projects that have 'Clinical Operation Service Transformation' selected.

I hope this makes more sense and I really appreciate you patience in helping me with this.

Best wishes

florian.zbinden7

hi,

then it's just a matter of adding an additional criteria with the proper range.

In both COLLECT and COUNTIFS functions you can have as many criteria as you want.

see here : https://help.smartsheet.com/function/collect

Just add it after the second criteria and you should be good.

TanyaMac

Thanks so much, that sounds easy enough.

Best wishes