Calculating the average of a column based on specific criteria in another group of cells


Hello everyone,

I am trying to calculate the average of a column based on criteria found in another. The catch is that I don't want to specify the specific text of the other criteria as it is going to be based off of an ever expanding drop down/list of different values and I want to minimize constant manual intervention. Essentially, I want to tell the formula to look at the criteria against what's selected as the dropdown in the criteria row.

Here is what I have, the third column represents what I'm trying to gather. So I would expect every row that is Google to show an average of 3 and every row that is Apple to show an average of 4. The formula I have tried with no luck is: =AVG(COLLECT([User Rating]:[User Rating], Name:Name, Name@row))

Name User Rating Average

Google 4 ???

Apple 5

Google 3

Apple 2

Apple 5

Google 2


Best Answer

  • Matt Foss
    Matt Foss ✭✭✭✭
    Answer ✓

    Please disregard as this has been solved! The "User Rating" column and its associated number is driven by formula that provides a number based on a text based rating scale. The source formula had the numbers in parenthesis which made the AVG function view it as a non-number. Thanks to anyone who was trying to solve!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!