Index / Match / Large / If help
Hi all,
I am trying to return the name of the top 5 salespeople for a given store. I have a working formula if all their sales came through on one line, however, they come through on multiple lines. So essentially, I want to sum the sales for each person, then rank them based on their total sales and group by location.
This formula returns a name, but only based on 1 line of sales - not the sum of all their sales.
{=INDEX($B2:$B21,MATCH(LARGE(IF($C$2:$C$21=$G$1,$D$2:$D$21),1),$D$2:$D$21,0))}
You must use the Pivot Table to visualize the desired output.
Convert your data into an Excel Table by selecting any cell in the data range and press Ctrl+T and hit OK to format the data range as an Excel Table.
Now insert a Pivot Table as shown in the attached and it will show you the top 5 salesmen for each location.
Once you add more data down the rows, all you need is, right click in any cell in the Pivot Table and choose Refresh and it will update the Pivot Table with latest info.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Thanks for the response Sktneer.I'll get working on it that way. Shame there isn't a way to get a formula to work!
Post deleted as better formula provided by FlameRetired.
Last edited by sktneer; 02-16-2018 at 02:17 AM.
If I understand correctly and you still would like to see a formula solution ... this is rather complicated and resource heavy.
To return the ranking totals this in I3 and filled down.Copy that range and paste into I10 and I17. You will need to change 3 references to the cities in I1 to I8 and I15 for the other sections.Formula:
Please Login or Register to view this content.
To get the names this in H3 and fill down.Copy/paste and edit as before.Formula:Please Login or Register to view this content.
Dave
Good one Dave!
Thank you sktneer, but I have to agree the pivot table is still the best solution.
Yeah, that depends upon personal preference. That's why I offered a solution based on Pivot Table.
Thank you very much, both of you!
I do have my reasons for wanting to stick to formulas. I have been asked to make one table that shows a huge range of information from 4 different brand stores, then 10 different locations. Things like sales/customers/costs/top 1/top 2 (73 columns all up) all at-a-glance. If I were to use a pivot table, it would be an additional dataset to manage, where my goal is to make what I already have all into one.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks