Employee Reviews - Averaging scores for different criterea


Been playing around with a few index/match formulas but getting a bit complicated..🤯

To explain:

  • Need to add numeric values to each dropdown to give scores to each critera
  • Then search for each Reviewer that reviewed each employee and add all scores for each critera
  • Finally, average the score =AVG(CHILDREN())

Went ahead and published both sheets if it helps for more reference.

Role Review Testing

Employee Review [METRICS]

Thanks for reading!



  • Mathieu PERSICO
    edited 07/24/20

    Hi Maricarmen,

    Do you have to follow exactly the Employee Review sheet or can we use a different template ?

    If second option, I think using a template similar to Role Review Testing would be easier in your case.


    Mathieu | Workflow Consultant


  • Maricarmen Vargas

    Ended up creating a helper colum "Employee+Reviewer" in order to be able to index and match both Employee and Reviewer criterias.

    =INDEX($Score$2:$Score$6, MATCH(INDEX({Complete Table}, MATCH($[Primary Column]$8 + " " + [Primary Column]@row, {Help Join}, 0), 3), $[Primary Column]$2:$[Primary Column]$6, 0))

    {Complete Table} is from Employee to Criteria 5

    {Help Join} is Employee+Reviewer

    If anyone has any other idea or optimization, let me know!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!