Counting Unique Names in a column
I've seen variations of this but essentially I have a list of projects and within that sheet is the name of the project manager. That name may appear more than one time as project managers manage more than one project at a time. I'd like to represent the project load per project manager in graph on a dashboard (if possible). I thought the first step would be to get a count of how many times that name appears in that column (accounting for criteria that would represent if the project is closed or not). Not sure if I do that in another sheet or report first before attacking it with a graph on a dashboard. Any insight to get me started would be appreciated
Answers
-
I would suggest pulling the counts onto a separate sheet.
Start with listing out each person once, then you can use a COUNTIFS to keep the numbers automated. When building your graph, reference this separate table, and everything should stay pretty clean for you.
-
John,
You're on the right track! I personally prefer to have my metrics on a separate sheet for a dashboard, but you could also tuck it somewhere in the source sheet. Having them on a separate sheet helps me stay organized when creating the dashboard.
Here's what I would do on a new sheet:
Create two columns: Project Manager (and list the PM names in this column), and Count. In the Count column, use this formula:
=countifs({Project Manager Range}, [Project Manager]@row, {Status Range}, "Open")
In the above formula, {Project Manager Range} is what I have named the cross-sheet reference to the PM column in your source sheet, and {Status Range} is what I have named the cross-sheet reference to the Status column in your source sheet. You would, of course, change the "Open" to whatever status you actually want to count.
You'll also want to keep in mind that if your source sheet has the PM column set as a Contact column, you'll want to set the Project Manager column in the summary sheet as a Contact column as well.
Hope this helps!
Best,
Heather
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!