I have a spreadsheet that lists Technicians (Col A), their territory (Col B) and their total sales (Col C). Col D needs to be the average sales for the territory listed in Col B.
The attached example only has 6 territories, but the actual spreadsheet has 20, so my IF formula won't work and I'm not sure how to get VLOOKUP to do this.
This needs to work while the workbook is sorted via different criteria, in Excel 2003 and 2010.
Any help is appreciated.
Mike
Last edited by mikeinfl; 02-22-2011 at 11:42 AM.
For compatability in Excel 2003, try this in D2 dragged down
=SUMIF($B$2:$B$14,B2,$C$2:$C$14)/COUNTIF($B$2:$B$14,B2)
Is that what you are looking for?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That works perfectly and is much simpler than anything that I was trying.
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks