Expand my Community achievements bar.

# Workfront

## Formula Help: Calculated Expression

Level 4
I am hoping others who are more skilled in this area can give me a hand. I have a calculated expression that works, and it works so well that the scope is being expanded and I don't know how to make this work. Background, we have a multi-select field named "Solutions Being Implemented". Original scope was to create a calculated expression that would generate the number 1 if option "TWN" was selected, generated the number 1 if "UC" was selected or generated the number 2 if both "TWN and UC" were selected. The goal was to display the TRUE number of solutions being implemented to visualize work effort. One project manager could be implementing 4 solutions so the resource manager needs to understand that even though they have 10 projects, they are REALLY managing 20, for example. Now, the original stakeholds want the calculated expression to total the number of options selected. 1 if 1 or 2 if two or 3 if three, etc. To them it doesn't matter what is selected, then just want to know how many solutions are being implemented per project. Is there an easier way to do this? Here is a view of my custom field... Here is my current formula: IF(CONTAINS("UC",Solutions Being Implemented),IF(CONTAINS("TWN",Solutions Being Implemented),"2","1"),IF(CONTAINS("TWN",Solutions Being Implemented),"1")) Jaclyn Reiter, PMP, SA Project Manager, Strategic Initiatives Equifax, Inc. St. Louis, MO 314-684-2693
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

4 Replies

Level 4
The simplest way I can think of is to use the SUM and CONTAINS function. In your example you'd want to have something like: SUM(CONTAINS("Compliance Center", {Solutions Being Implemented}), CONTAINS("I-9", {Solutions Being Implemented}), CONTAINS("WeVerify", {Solutions Being Implemented)) And so forth adding another CONTAINS statement for each possible value of the checkboxes. James Bender Jackson/JTS

Level 3
Hi Jaclyn, You might want to check out Melinda Layten's post for a great alternate approach. "https://community.workfront.com/discussions/community-home/digestviewer/viewthread?MessageKey=cc68cd94-ba57-4629-88b2-31b67cc3dcc2&CommunityKey=aaafaff0-5e4e-4e38-8903-f1f990935567&tab=digestviewer#bmcc68cd94-ba57-4629-88b2-31b67cc3dcc2">Here's the link . Kathy

Level 4
Thank you @James Bender . Your solution works! Jaclyn Reiter, PMP, SA Project Manager, Strategic Initiatives Equifax, Inc. St. Louis, MO 314-684-2693

Level 4
@Kathy Leeman , I really appreciate you reminding me of the alternate approach. I remember seeing this previously, and I am going to have to wrap my brain around it. I really like this approach, but it is somewhat outside of my area of expertise, so, I'm going to have to just play to my strengths for the moment and take my time getting the formula to work. Thanks so much!! Jaclyn Reiter, PMP, SA Project Manager, Strategic Initiatives Equifax, Inc. St. Louis, MO 314-684-2693