Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a total adding cells from other sheets for a roll-up | Excel Worksheet Functions | |||
adding total entries from one page to show on another | Excel Discussion (Misc queries) | |||
Help matching entries in two sheets??? | Excel Worksheet Functions | |||
Adding a Single Cell total from Seperate sheets | Excel Worksheet Functions | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions |