Average Results for Sales Team Across three years

[ottawacubbie]

Hello,

I currently have five years worth of data on five different sheets. Each sheet has the results of the 150 sales managers names and records for that year. I want to make an average of their records while with the company over this time. However, each year there is 10 or 15 new managers and three or four that have left. I want to include the records of everyone ever with the company. If this isn't clear I hope this helps.

Sheet 1 2009
Fred $100
Mary $150
Alice $50
Mark $200
Greg $200

Sheet 2 2010
Fred $200
Mary $120
Daniel $250
Mark $200
Norm $100

Sheet 3 2011
Fred $200
Telly $120
Daniel $250
Mark $200
Norm $100

Is there anyway to have Excel find the exact name matches and give me the averages?

[NBVC]

If you have a list of all the unique names in your summary sheet, say starting at A2, then try something like:

Please Login or Register  to view this content.

where sheets are named by the year, e.g. 2009, 2010, 2011, 2012 (adjust the year ranges in the formula to match)... and assuming data is in columns A:B in all sheets.