I need to collect the average of a column by referencing name in another column

I have columns in a sheet per the screen below. The Team Member is input through a form submission and can potentially be input many times. I am looking for a formula to collect the average score from all rows where one name appears without having to say ="Scooby Doo".


Best Answer


  • L_123
    L_123 ✭✭✭✭✭✭

    =avg(collect([% max score]:[% max score],[team member]:[team member],not(contains(" ",@cell

    The formula above will give you the average of all of the values that only have a single word in the team member column if that is what you were looking for.

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭

    @L@123 Thanks. What I am looking for, is for the combined score for Scooby Doo, and a separate total score for Shaggy, and so on. I want to be able to do this without have to say =Scooby Doo or =Shaggy. I will not always know the list of names that are added.

  • Lindsay AR
    Lindsay AR ✭✭✭✭✭
    Answer ✓

    Update: I just exported to Power BI and did all my calculations there.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!