Formula to Count Items in a Multidropdown List - Modification of Portfolio Metrics

davidhorton45

Hi! I am trying to count items in a Multidropdown list, and using the Portfolio Metrics standard sheet from the PMO Template. Right now the sheet has the below formula but it is not returning the correct number of Projects by Status that are Not Started for the Product Team. I tried a COUNTIF with CONTAINS but that did not work. Any suggestions?

=COUNT(COLLECT({Project ID}, {Project Status}, $Label@row, {Primary Delivery Teams}, Product$1))

Leroy Noriega

Try:

=COUNTIFS(range,CONTAINS("Specific Text", column@cell))

Kerry St. Thomas

There's a formula specifically designed to count how many items exist in a multiple-select dropdown column. COUNTM. I'm not exactly sure what the specifics of your sheet layout is, so I admit I'm having a bit of trouble parsing out the rest of the formula; sorry!

Check this out for a bit more info:
https://help.smartsheet.com/function/countm

davidhorton45

@Kerry St. Thomas So I have this sheet which has the Teams I am trying to reference from the Dropdown list (Product, A, B, C, etc.) in each of the columns and then the status for each of those team's projects which is in my Project Intake sheet (separate sheet). So would the formula COUNTM just replace what is in collect?

davidhorton45

As a follow up to the above COUNTM does work in returning that Product was selected as one of the three teams that are working on Project A, but I also need to know the status of Project A. So in this example if there are 5 total projects, Product team was a part of 3 of those projects and the status of those projects were

Not Started Red

In Progress Yellow

Complete Green

then the column for Product team should essentially read 1, 1, 1, 1, 1, 1. Let me know if this makes sense and thanks a bunch for your initial response!

davidhorton45

@Leroy Noriega would love your input as well if you know the answer and I also appreciate your original comment!