Display Top 20 results based on certain criteria

Dear All,

In the attached sheet I need to display the list of top 20 customers at a location level. The formula should essentially count the top 20 records and sum up the Past Due numbers in column C. The important thing to note is that it should count repetitive customer numbers or their names as one and then display the results. Essentially the same result one would get by using the sort option in a pivot table.

I have partly achieved success in this by using a combination of sum & large functions (array). However I am stuck when it comes to adding up the duplicates and also the locations.

Could anyone please help me out with this?

Thank you in advance for your help.

Kind Regards,
FS

hey ash_farooq..
hope your problm is solved now ..

Thank you MarvinP for your proposed solution. However I have a constraint not to use pivot table, hence looking for a formula based alternative.

Regards,
FS

Thank you Kamalchandra. Can you please tell me how to refine this further and get the total values at a location level? (That is the $ value of past due for each locations)

Regards,
FS

Hi FS
if you want to make this dynamic for location then you have to make a unique combination of location and customer name then by using this file you will be able to get the desired result.as it was given.

or if you wish to make sum of location-wise the make a unique list of location , then use sumif and you will get the result.

check the file.