matching entries in different sheets, then adding their total?
 

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.

matching entries in different sheets, then adding their total?
 

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$10 0<""),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
---
"drumbumuk" wrote:
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.