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)

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!