Need to count project managers and number of projects

06/27/19 Edited 12/09/19

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.


smartsheet help.JPG

project leader.JPG

Popular Tags:


  • Mike WildayMike 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 = [email protected], @cell = [email protected]))

    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:


  • This worked like a dream! Thank you so much!

  • Mike WildayMike Wilday ✭✭✭✭✭

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

  • 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.

Sign In or Register to comment.