Averaging scores with certain criteria

I would like to get averages of scores of applications sent in considering certain criteria.

There are applications that are submitted, and each application is scored by 5 people.

You can see these applications are named Q3-03, Q3-04 etc....

Sometimes the scorers recuse themselves from scoring and their score appears as zero, but they just refuse to score that particular application, and their zero shouldn't be counted in the average.

Sometimes not all the scorers have submitted their scores at the time, but whoever has turned in a score I would like to be included in the average.

I'm new to functions and been trying to figure this one out and having a hard time. I'm hoping I can get some help. Thank you!



Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    Use a SUMIF/COUNTIFS

    Example

    =(Sumif([Application ID/Name/Category]:[Application ID/Name/Category],Q3-03))/(COUNTIFS([Application ID/Name/Category]:[Application ID/Name/Category],Q3-03,[Score]:[Score],>0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!