Add additional portfolio metric to count certain criteria within the Project Categories

I'm using the PMO Template Set and preparing it to use for a portfolio of a different type of projects at our enterprise. (I'm already successfully using the PMO Template Set for a simpler portfolio type and I want to keep these separate for portfolio dashboards/metrics/etc., hence creating an additional workspace/portfolio.)

I'm not sure that what I want to achieve can be solved using a formula on the portfolio metric sheet.. Here goes!

We'll have 6 Categories of Project Types, and I already successfully added the extra 3 to the Intake Sheet and the Portfolio Metrics sheet. Pretend they're called Category 1 - Category 6.

What I'd like to do now is (if it's possible), add additional metrics to the Portfolio Metrics sheet, such as this:

For Category 1 (for example), provide sum of how many items were input via the drop-down on the intake form for a particular column — let's call that column "Distinct Ingredients". (It's not that, but so you can visualize.)

So the metric output I'm looking for is the sum of all "Distinct Ingredients" for all projects under Category 1 (and I'll do that for all the other Categories as well).

So the portfolio metrics sheet would show Category 1 has 24 Distinct Ingredients, Category 2 has 34, etc..

Here's the normal formula on the metrics sheet when looking for a particular "status" — like "In Progress", where "In Progress" is the Label at the portfolio metrics sheet row ($Label@Row), but altered slightly to try to show my criteria column names:

=COUNT(COLLECT({Project ID}, {Project Category}, [Category 1]$1, {Distinct Ingredients}, $Label@row))

I need help trying to modify that formula so that portfolio metrics just tries to SUM up all Distinct Ingredients for all Category 1 project types.. I've used "COUNTM" instead of COUNT, as well as SUM instead of COUNT. (I have two situations, one column has multiple entries in the field, hence the COUNTM; and the other situation is just numerical, where I want the SUM. I figured if somebody can help me with either one situation, I can figure out the other.)

Also, I get that the Label@Row reference is wrong for this, since there isn't a Label available to show at the portfolio metrics sheet row.. but I can't make the formula work by eliminating it, either.

Also, I'm confused by the formula's first entry — the range = {Project ID}.. I don't understand Project ID as the range at all.

Thank you to anybody who has the patience to read through this : )

Sharon

Find more posts tagged with

Accepted answers

All comments

amarkumar

https://community.smartsheet.com/discussion/144447/add-additional-portfolio-metric-to-count-certain-criteria-within-the-project-categories

To sum a number column for all Category 1 projects:
=SUMIF({Project Category}, "Category 1", {Distinct Ingredients})
If you need to count items when cells have multiple entries (like comma-separated):

Add helper column on intake:
=COUNTM([Distinct Ingredients]@row )
On metrics sheet:
=SUMIF({Project Category}, "Category 1", {Helper Column})

Replace {Helper Column} with the actual name of your helper column.

Paul Newcome

Are you able to provide screenshots with sample data for context?

ShaRobinson

Thank you for your quick assistance. I didn't need helper columns for either solution, I just needed to better understand both setting up Cross-sheet references and also the COLLECT function. I was misunderstanding the first 'range' in the collect function.

I feel MUCH better now about how this! My formulas work.. I feel like I just grew wings and can fly! : )

ShaRobinson

Hi Paul, thanks for jumping in to help.

I just needed to better understand both setting up Cross-sheet references and also the COLLECT function. I'm all set now.

Paul Newcome

I see more info has since been added to your post. Glad you were able to get it sorted!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.