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

Bolie

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.

Mark.poole

@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?

Bolie

@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

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