Multi-select Dropdown and multiple values

Del Horne

Hi community!

Looking for some guidance please! :)

I need a column capturing the number of specific job roles associated with project.

e.g.
Job Roles
----
Product Manager
Delivery Manager
Software Engineer
Software Engineer
Tester
Tester

I don't think this can be done via a multi-select dropdown with multiples of the same values?
Wonder how best I could achieve this without having a column to capture the amount per job role as I have 20+ job roles.

Thanks in advance!

Del

NicoLHC

Hi Del! 😊

Thanks for reaching out to the community with such a thoughtful question! It’s clear you’re trying to find an efficient and scalable solution for tracking the number of job roles without cluttering your sheet—great thinking! Let me walk you through a couple of approaches that I think will work perfectly for your needs.

Option 1: Helper Column with a Simple COUNTIF

One way to do this is by adding a helper column that counts how many times each specific job role appears in the "Job Roles" column.

Here’s how you can set it up:

1. Create a column called something like "Count of Job Role."
2. plaintextCopy code=COUNTIF([Job Roles]:[Job Roles], [Job Roles]@row)This formula will scan the entire "Job Roles" column and count how many times the role in each row appears. For example:
   • Product Manager → 1
   • Delivery Manager → 1
   • Software Engineer → 2
   • Tester → 2

Now you’ll have a clear count for each role in every row!

Option 2: Summary Table for Unique Job Roles

If you want a cleaner, bird’s-eye view that lists each unique job role with its total count, you can create a separate summary table. This is perfect since you mentioned you have 20+ roles, and it keeps things organized.

1. In a new section or sheet, list each unique job role (manually or using a dropdown list for reference).
2. plaintextCopy code=COUNTIF({Job Roles Column}, [Job Role in Summary]@row)This formula pulls data from your main sheet and gives you the total for each role. For example:
   • Product Manager: 1
   • Delivery Manager: 1
   • Software Engineer: 2
   • Tester: 2

This summary table will let you see counts for every role at a glance, and it’s easy to update if your roles change.

Option 3: Multi-Select Dropdown with Role Counts

If you prefer working with a multi-select dropdown where roles are listed in a single cell (e.g., "Product Manager, Tester, Tester"), we can still calculate counts using a formula to find specific roles:

1. plaintextCopy code=LEN([Multi-Select Column]@row) - LEN(SUBSTITUTE([Multi-Select Column]@row, "Tester", ""))Replace "Tester" with the role you’re tracking. This approach works if you’re comfortable managing counts manually for multiple roles.

My Recommendation

For 20+ roles, I’d suggest going with Option 2: Summary Table. It’s clean, scalable, and gives you the flexibility to quickly see totals for each role without overcomplicating your main sheet. Plus, it’s easy to maintain if new roles are added or existing ones change.

If you’d like me to dive deeper into any of these steps or help troubleshoot, I’m happy to assist further! Let me know how this works for you. 😊

All the best,
Your friendly Smartsheet helper! ✨

Del Horne

Thanks Nico,

These are great suggestions on the count, which I can definitely implement.
I was more thinking - how can I setup a column that allows me to populate a cell with the roles that are part of a project?

Column: Project Name
Project X

Column: Job Roles
Product Manager

Delivery Manager

Software Engineer

Software Engineer

Tester

Tester

Can I use a multi-select dropdown to select multiple of each role? Or is there another approach? I want to avoid having a column per job role with corresponding value if possible

Thanks!

Del