Creating dynamic top 10 list with multiple conditions and criterias

So this is going to be somewhat complicated to explain. Everyone tells me there is no solution to it.. however I believe there has to be one!


I need to create a top 10 list without having to pivot or update any formulas or manually add anything as this will be a monthly process; I need this to be as automated as possible so that whenever I download new raw set of data I automatically receive the top 10 list. before I give you an example you should know that the titles of the columns do not change from month to month and neither do the type of products. my raw data has 36 different columns however i only need my formula to search through 3-5 columns out of the 36.

I will give you an example out of the 5 columns: Column 1) is product and there are 4 products however I only want to look through 3 of the 4 products in the data. Column 2) is name, there are multiple names and duplicate names as there are many transactions so I would need the formula to add the matching names together to see which are the largest. Column 3) is Sales. Column 4) is whether this is national or international sale, I need to create a formula for both and one for international only. Column 5) will be whether its dollars or pounds. So to sum it up I need to return the top 10 clients by filtering out 3 of the 4 products. I am currently using an Index Match Large formula however it looks through all 4 products instead of just 3. I tried using sumifs however I am not sure how to combine them into the index match large formula and every time I do I receive N/A. If anyone can help I would really appreciate it.

attached will be an example


My first formula which will be on a different sheet will give me the top 10 names based on only 3 of the products.
my second formula which will also be on a different sheet will give me the top 10 names out of the same 3 products however I they need to be international sales only.

I only need the names.

I apologize if this was too long. Thank you to anyone who tries in advance. I hope someone here can help me.

thanks guys

Hi, and welcome to the forums

I'm sorry... I sort of understand your situation, but isn't able to gasp your desired outcome.
1.) Are you going to have 3 cells to select the 3 products? (of the 4)
2.) How do you determine your "top 10"?
3.) Does "Sales" mean price in your corresponding currency unit, or is it the number of goods sold?
4.) What is your desired output format, and what should be included?
5.) and etc...

Is it possible for you to include a few samples of your expected outcome?
Sorry again that I'm not bright enough, but I hope that I can be somewhat of assistance.


SC

see the attached file