New Count Formula in Portforio Metrics Not Counting New Group Correctly

Michele L

Hi,

I created a new group in Portfolio Metrics sheet of PMO Template. I want to count the Project Length of each active project and display on PMO dashboard.

I have created a # of Days Column on Project Intake and then created helper column with formula that determines Project Length Bucket. I added new Grouping to Portfolio Metrics Sheet of Project Length. This is the formula i have in my portfolio metrics sheet to count the project by what length bucket they are in:

=COUNT(COLLECT({Project ID}, {Project Status}, "In Progress", {Project Length}, $Label@row))

References are: {Project ID}, {Project Status}, {Project Length}

The Project Length Buckets in Portfolio Metrics are:

1-90

91-180

181-365

>365

There are only two projects in my portfolio, with one project in 1-90 and and project in 91-180. But the sheet is displaying a count of 1 in every bucket. Please see attached images.

What am I doing wrong? Help I am brand new to Smartsheet.

Genevieve P.

Hi @Michele L

You're very close! 🙂

I believe you're looking for a COUNT(DISTINCT(Collect? That will count how many distinct Project IDs there are with your other two criteria... meaning if there are duplicate rows it will skip them. Is this what you wanted?

Try:

=COUNT(DISTINCT(COLLECT({Project ID}, {Project Status}, "In Progress", {Project Length}, $Label@row)))

Cheers,

Genevieve

Michele L

Hi Genevieve,

Thanks for trying to help.

After screwing up the Portfolio metrics formulas, I copied the the template portfolio metrics sheet to my workspace. I think that caused all the links to get corrupt. I rebuild all the formulas from scratch with fresh reference links and now it works.

Also helped me get better at formulas ha ha.