Help with Formula to Count Projects

aneves

Hi all,

I'm trying to create a formula in Smartsheet that counts the number of projects a team member is assigned to. In the report I have, the names are listed as first initial + last name (e.g., JDoe), but I want to summarize them using last name, first name (e.g., Doe, John). I was considering using a CONTAINS function to match the names, but I'm not sure how to make it work without manually updating the list.

Is there a way to count the projects assigned to each team member using the different name formats without updating my list manually? Any advice would be appreciated!

Thank you in advance!

Adam Murphy

Hi, I would use a second sheet for generating the metrics. I would have a column for first name, and a column for last name, then add a helper column that simply uses a formula to "recreate" the JDoe entry (use the LEFT function to extract first letter of firstname + lastname), then lastly I would count using a countif that matches my recreated name against the main sheet name. So my metric sheet would have 5 columns (lastname comma firstname, firstname, lastname, username, count). Then you can report (or make charts/graphs) from the metric sheet to show your results. Does that make sense?

aneves

Hi Adam,
Thanks for your suggestion. Would you mind outlining the recreate formula, I am not familiar with it.

Thanks

AravindGP

Hi @aneves

Here's the formula you can use. =LEFT([First Name column]@row, 1)+[Last Name column]@row)