# AverageIf Dropdown (Multi-Select) cell contains an attribute/value

Options
✭✭✭

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)

• Employee
Options

You can use a combination of AVG(COLLECT to do this!

Since your Objectives are listed in the Parent row of the same column in your second sheet, you will need to reference the Parent as part of your criteria. In addition, since your Key Results column is a multi-select column you'll need to add in a HAS statement when looking for the Key Result in that row.

Try this:

=AVG(COLLECT({Project % Complete}, {Objective in other Sheet}, PARENT([Objective/Key Results]@row), {Key Results in other Sheet}, HAS(@cell, [Objective/Key Results]@row)))

This will AVG the values in the % Complete column, but only if the following criteria are matched:

• The Objective in the other sheet = the Parent (the Objective) of this row in the Objective/Key Results column
• The Key Results in the other sheet = HAS the Key Result in this row

Here are some Help Center articles I used to build this: COLLECT Function / Cross Sheet Formulas / @cell and @row / PARENT function / HAS function

Let me know if this works for you, or if you have any other questions!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee
Options

As a quick follow-up, I realized you'll receive an error if there are no matches. To fix this, wrap an IFERROR Function around the whole formula:

=IFERROR(AVG(COLLECT({Project % Complete}, {Objective in other Sheet}, PARENT([Objective/Key Results]@row), {Key Results in other Sheet}, HAS(@cell, [Objective/Key Results]@row))), "")

October 8 - 10, Seattle, WA | Register now

• ✭✭✭
Options

Thank you @Genevieve P & @Paul Newcome . This is perfect and exactly what I was needing.

• Employee
Options

Great!! Happy to help

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
edited 03/30/20
Options

@bburrets

Apparently I was mistaken in some info I provided. CONTAINS and HAS actually work the same way when referencing a multi-select type of column. I will edit my post to correct that.

@Genevieve P Thanks for setting me straight. Haha.

EDIT: I tried to edit my original post, but it won't let me. I am assuming it is because it has already been flagged as a "Best Answer".

All, please note that my solution using the CONTAINS function is actually only a second option. Genevieve's solution using the HAS function works just fine. I was mistaken.

• Employee
Options

Haha no problem!

@bburrets the only reason I'd use HAS over CONTAINS is in case you had some other value that had "Retire" in it... for example, if you had "Retire" and "Retire Again" but only searched if the cell CONTAINS "Retire", you'd receive data for both of these. In your instance it looks like all of your values are unique, so you can use either option. 🙂

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

@Genevieve P Please see my edit above. I can't edit my original comment. Normally I would just not worry about it, but I don't want incorrect information being flagged as the "Best Answer". Is there any way I can get that taken care of whether it be to flag my correction so that it gets bumped to right underneath or switching the "Best Answer" from my response to yours?

• Employee
Options

As a Moderator, I was able to adjust your comment - it's still a correct answer so I've left it as Accepted (I hope that's ok!)

Let me know if there's anything else you'd like to add to that first post and I'm happy to paste it in. 🙂