matching entries in different sheets, then adding their total?

[drumbumuk]

On sheet1 I have a list of suppliers that we buy from. In sheet2 I have
asked colleagues to rate the supplier experience over a number of criteria.
The row is one record of one experience. At the end of the row a rating is
calculated. I want to return this rating to Sheet1. I can do this with
VLOOKUP - but here is the tricky part - there maybe multiple entries on
Sheet2 for one supplier. I want to calculate the average rating for these
multiple entries and then return the result to the suppliers record on Sheet1.

I have been up all night - i know it can be done. But cant find the answer.
XL 2003 in use.

 

[Max]

Assume that in Sheet2, supplier ids are in col A, with corresponding ratings
in col B, data from row 2 to say, row 100

In Sheet1,
Assume that the unique supplier ids are listed in A2 down
Put in B2, then array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula:
=AVERAGE(IF((Sheet2!A$2:A$100=A2)*(Sheet2!B$2:B$100<>""),Sheet2!B$2:B$100))
Copy B2 down. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik