Dynamic Top N List with SUMIFS

[Trepidatioussaurus]

Good day everyone,

I've searched several forums but couldn't find an answer to my question. Maybe it's not even possible in Excel but we'll see...


So, I've got my table with three columns Region, Shoe Size and Sales. What I want to achieve is to create a Top N list of shoe sizes and their corresponding sum of sales. For region A, this would be sizes 45 and 44. Should another region be selectet, also the Top 2 sizes might change, like 44 and 43 for region C:

I was not able to set up this highly dynamic Top 2 list with my Excel version Professional Plus 2016.

A solution without using pivot table is what I would need.

Is this even possible?

Thanks in advance for your replies!

Kind regards
Stefan

 

[Trepidatioussaurus]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1							Region	A
2
3	Month	Region	Shoe Size	Sales			Top 4	Shoe Size	Sum Sales
4	Jan	A	X43	5	35		1	X43	35
5	Feb	A	X44	15	15		2	X45	30
6	Mar	A	X45	30	30		3	X44	15
7	Mar	A	X43	15			4	X46	10
8	Apr	A	X43	15
9	Mar	A	X46	10	10
10	Jan	B	X44	10	10
11	Feb	B	X43	15	15
12	Apr	C	X43	15	15
13	Mar	C	X44	25	25
14	Jan	D	X45	20	20
15	Feb	E	X46	10	10
16	Jan	E	X43	5	5
17	Feb	F	X42	10	10
18
Main

Cell Formulas
Range	Formula
H4:H7	H4	=INDEX($C$4:$C$102,AGGREGATE(15,6,(ROW($C$4:$C$102)-ROW($C$4)+1)/($B$4:$B$102=$H$1)/($E$4:$E$102=I4),COUNTIFS(I$4:I4,I4)))
I4:I7	I4	=AGGREGATE(14,6,$E$4:$E$102/($B$4:$B$102=$H$1),ROWS(I$4:I4))
E4:E17	E4	=IF(COUNTIFS(C$4:C4,C4,B$4:B4,B4)=1,SUMIFS(D:D,B:B,B4,C:C,C4),"")

This works excellent! Thank you so much for your time!

 

[Fluff]

You're welcome & thanks for the feedback