Workload calculation - assigning a value to a specific person in multiple contact column

Options

Hi all,

I'm trying to calculate workload from several columns (multiple contact)

My team members are Contact A, Contact B, Contact C, Contact D, Contact E, Contact F, there could be other contacts listed in these 3 columns, e.g. Contact Z, Y, X, etc.

Generally, person listed under "Project Manager" would have a score of 5, "Project Core Team" would have a score of 3, "Project SME" a score of 1.

I am only computing the effort of my team members (A-F) depending what kind of role they play in each project .

Formula I'm using currently doesn't work when we have 2 or more contacts in the same column:

=IF(OR(HAS("Contact A", [PROJECT MANAGER]@row), HAS("Contact B", [PROJECT MANAGER]@row), HAS("Contact C", [PROJECT MANAGER]@row), HAS("Contact D", [PROJECT MANAGER]@row), HAS("Contact E", [PROJECT MANAGER]@row), HAS("Contact F", [PROJECT MANAGER]@row)), "5", (IF(OR(HAS("Contact A", [PROJECT CORE TEAM]@row), HAS("Contact B", [PROJECT CORE TEAM]@row), HAS("Contact C", [PROJECT CORE TEAM]@row), HAS("Contact D", [PROJECT CORE TEAM]@row), HAS("Contact E", [PROJECT CORE TEAM]@row), HAS("Contact F", [PROJECT CORE TEAM]@row)), "3", (IF(OR(HAS("Contact A", [PROJECT SME]@row), HAS("Contact B", [PROJECT SME]@row), HAS("Contact C", [PROJECT SME]@row), HAS("Contact D", [PROJECT SME]@row), HAS("Contact E", [PROJECT SME]@row), HAS("Contact F", [PROJECT SME]@row)), "1","")

Appreciate if you have any other suggestions to workaround this.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Bolie

    From what I can see. your contacts are not using a multi select dropdown. if that is the case have you tried using CONTAINS instead of HAS?

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Bolie
    Bolie ✭✭
    Options

    @Mark.poole Thank you for your response! I am indeed using the multi-select contact type, but I intentionally omitted the names due to P&C concerns. I am utilizing the HAS condition because I am working with contacts

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 11:31AM
    Options

    @Bolie

    IT took me a moment to figure it out. but to be sure. are you wanting a sum of all the points based on how many of your people fall in each category? ex. Project Manager = number of people * 5, Project core team = Number of people *3, and Project SME team = Number of people? If So

    =SUM(COUNTIF([Project SME]@row, HAS(@cell, "Contact A")), COUNTIF([Project SME]@row, HAS(@cell, "Contact B")), COUNTIF([Project SME]@row, HAS(@cell, "Contact C")), COUNTIF([Project SME]@row, HAS(@cell, "Contact D")), COUNTIF([Project SME]@row, HAS(@cell, "Contact E")), COUNTIF([Project SME]@row, HAS(@cell, "Contact F"))) + SUM(COUNTIF([Project Core Team]@row, HAS(@cell, "Contact A")), COUNTIF([Project Core Team]@row, HAS(@cell, "Contact B")), COUNTIF([Project Core Team]@row, HAS(@cell, "Contact C")), COUNTIF([Project Core Team]@row, HAS(@cell, "Contact D")), COUNTIF([Project Core Team]@row, HAS(@cell, "Contact E")), COUNTIF([Project Core Team]@row, HAS(@cell, "Contact F"))) * 3 + SUM(COUNTIF([Project Manager]@row, HAS(@cell, "Contact A")), COUNTIF([Project Manager]@row, HAS(@cell, "Contact B")), COUNTIF([Project Manager]@row, HAS(@cell, "Contact C")), COUNTIF([Project Manager]@row, HAS(@cell, "Contact D")), COUNTIF([Project Manager]@row, HAS(@cell, "Contact E")), COUNTIF([Project Manager]@row, HAS(@cell, "Contact F"))) * 5

    Will give you the totals.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!