Stuck creating formula that sorts highest to lowest then sum quantity

Hello

I have attached a replica file which i will use to duplicate solution (if any) to live file - please can you take a look?

In the 'Main' sheet, cell B3, i am trying to return the shop with the highest quantity of 'complaints'; by adding up values in column M in the 'MON' sheet. The 'shop' range in the 'MON' sheet is in column E.

I would like to also return the counted quantity of that query, in D3, of the 'Main' sheet.

So in this example i am expecting shop '234' in B3 (main sheet) and '48' in D3. So '234' is the shop with most complaints, and the quantity of complaints was 48.

I'll copy the formula into the other cells in the 'Main sheet'. E.g. i'll do the same for Column E, H, & K (same with the counted quantity, column D, G, J & M)

I'll also copy these down to the other highlighted boxes.

I understand this might be a two part formula? Sort then sum? Not sure!

Hope this makes sense

Thanks
Z

Try this formula, Array confirmed, in D3

=MAX(SUMIF(MON!$E$2:$E$20,MON!$E$2:$E$20,MON!$M$2:$M$20))

Then this one, Array confirmed, in B3

=INDEX(MON!$E$2:$E$20,MATCH(D3,SUMIF(MON!$E$2:$E$20,MON!$E$2:$E$20,MON!$M$2:$M$20),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

To make the formula work when you fill down would require the use of INDIRECT to use the weekday in column A of Main as the sheet reference. I would be reluctant to provide a formula that does this as there could be as the number of rows could be different each day.

This could be achieved with some dynamic named ranges, but given your data layout, they would need to be set up carefully!

Thanks

This one works!

I was able to copy right and down; just had to be careful replacing correct location