Counting Unique Names in a column

John Kostenbader

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

Paul Newcome

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.

Heather Duff

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