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

bburrets

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)

Paul Newcome

I agree with @Genevieve P however I would use the CONTAINS function as an alternative instead of HAS.

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

Genevieve P.

Hi @bburrets

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

Genevieve P.

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))), "")

Paul Newcome

I agree with @Genevieve P however I would use the CONTAINS function as an alternative instead of HAS.

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

bburrets

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

Genevieve P.

Great!! Happy to help

Paul Newcome

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

Genevieve P.

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

Paul Newcome

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

Genevieve P.

@Paul Newcome

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

Paul Newcome

@Genevieve P That'll work just fine. Haha. Much obliged!