AverageIf Dropdown (Multi-Select) cell contains an attribute/value
Hi Smartsheet Community, thanks in advance for all your help. I've been able to learn so much from the help you provide to others.
I have two sheets I am working with for this experiment:
- A master project list, with unique projects aligned to an objective and the option for different key result attributes (Drop down Multi-select). Additionally these projects all have a project completion values.
- A second "Results" sheet holds a list of objective along with each possible key result.
I would like to return the average of the projects' completions based on the key result multi-select in the Master project list. I've been successful in getting a formula to work when there is one single Key Result selected for the project, but when there are multiple Key results attributed to a project, the formula breaks.
I believe this requires a combination of (SumIfs/CountIfs) or AverageIf or even Average(Collect(). I've tried several combinations but what I seem to keep running up against is the criterion syntax in the formula to evaluate the multi-drop down box.
I've attached pictures of my examples:
"Master Project List":
"Results" Sheet: (w/ Explanation column & highlighted desired results where formula needs to go)
Help Article Resources
Check out the Formula Handbook template!