Need to count project managers and number of projects

Melissa Shoumlisky

I'm hoping the smartsheet Gods can help me.

I'm struggling.  I have 126 projects in my sheet and about 30 different project leaders.  Each project leader is set up as a contact (see 2nd screentshot - Pink)

I'm trying to count projects by project lead.  

I'm hoping to come up with something like the first screenshot.

Any ideas are greatly appreciated.

Thank you.

Melissa

Mike Wilday

In a new smartsheet you can set up a formula with cross-sheet references to do this. Set up the column of users email addresses in one column and user names in another then in the third column add the following adjust the column titles accordingly: 

=COUNTIFS({People at work}, OR(@cell = Emailcolumn@row, @cell = NameColumn@row))

I check for both the display name and the email as in some instances smartsheet looks for one but not the other. In my screenshot, I couldn't find the Mike Wilday tasks by looking for email, it was only referencing the Name, but craig's tasks were only counting his email. Clearly smartsheet has a bug for accessing the email column and how it references them. 

How to insert a cross-sheet reference: https://www.youtube.com/watch?v=u01GHwmlDRA

Melissa Shoumlisky

This worked like a dream! Thank you so much!

Mike Wilday

Awesome! Glad I could help you out. Happy Smartsheeting! 

Kevin Antene

I have a similar project, minus the defined contact options.

I have a sheet with a list of projects. It includes a column of broker names; which could really be any variable. I'm trying to build a report that will list each unique broker name and the total number of times it appears in the list. Any thoughts?

I'm new to smartsheet and loving it, but it has been a really long week and my brain is melting.

JEastep

What if you have multiple project leads/managers? How would a formula work to count for every project they are assigned to?