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!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hey @MeganJF

    I agree with @Hollie Green! You can use an AVG(COLLECT to average a column based on multiple conditions.

    An AVG(COLLECT works like this:

    =AVG(COLLECT({Range to Avg}, {First Criteria Column}, "Criteria 1", {Second Criteria Column}, "Criteria 2"))

    So in your case, try something like this:

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


    See: Average with multiple conditions

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!