Formula trouble: Average score for specific word in multi-select column

I have scrubbed the community board for this answer, but was unable to find the formula I needed. Therefore I am sending out an SOS. :)

On the master sheet, I am calculating scores and an overall total average.

Example formula: =AVERAGEIF([NE: Value]:[NE: Value], <>"") -- This is working

On my metrics sheet, I would like break those down to show the averages of scores within the assigned business unit "BU".

Here is what I have tried so far:

=AVERAGEIF({Range NE:Value} <> "",{Range BU}, [item2]@row)

=AVERAGEIF({Range NE:Value} <> "",AND({Range BU}, CONTAINS("Payments"@cell)))

*Item2 = Payments

As you can see from the formula, I'm also wanting to exclude blank cells (no assigned score)

Am I even close??

Thank you in advance!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!