Formula for taking an average of duplicate rows
I'm attempting to construct a formula for taking averages of multiple ratings from several raters across three vendors.
Sheet 1 looks like:
Rater  Category  Vendor A Rating  Vendor B Rating  Vendor C Rating
Sheet 2 looks like:
Category  Vendor A Rating Avg  Vendor B Rating Avg  Vendor C Rating Avg
On sheet two, I need to get the average rating for each category and vendor. The formula should look at sheet one and determine where the category matches and then average the values in that row. I believe in Excel there is a AVGIF function to do this.
Any help is appreciated.
Comments

It sounds like you want to use the COLLECT function:
=AVG(COLLECT([Vendor A Rating]:[Vendor A Rating], Category:Category, “Category X”))
In this example, the function takes the average of all Vendor A ratings where the category is Category X.
More info here: https://help.smartsheet.com/function/collect

Hi all,
This may help in the future:
=AVERAGEIF(range, criteria, [average_range])
Returns the average of cells that meet a given criteria.Example: =AVERAGEIF([Value 1]200:[Value 2]280, >100)
Best,
Kara
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 216 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!