Average calculation on survey AVG(Collect)?


We have a survey form for multiple programs with 20 criteria for rating/scoring. I want to create a separate summary sheet that shows each Program Name, and the average score for each criteria. Please advise the best cross sheet formula to use for this.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Yes Sharon,

    AVG(COLLECT) is the formula you need. It has the syntax AVG(range to be averaged, range1, criteria1, range2, criteria2, range3, criteria, etc). You can add as many or as few criteria as needed as long as they are added in range-criteria pair.

    If you need any assistance with your cross sheet references, let us know.


  • Sharon Blanchard

    Hi Kelly,

    This is the formula I've tried and it's not working (I am getting a #divide by zero error) I have a Program column and a Trust column on another sheet, and I want to calculate: If the program name is Johnson and Johnson, what is the average score for trust.

    =AVG(COLLECT({Program}, {Trust1}, OR(@cell = "Johnson and Johnson", @cell > 0)))

    I'm thinking that I'm doing something wrong with the Program name because I dont' want the average of that. Thanks so much if you can assist!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!