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

 

[Fluff]

Hi & welcome to MrExcel.

Can you post your actual data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Sufiyan97]

Hi & Welcome to MrExcel

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Reegion	Shoe Size	Sales		Region	A			Region	C
2	A	43	5		Top 2	Shoe Size	Sum Sales		Top 2	Shoe Size	Sum Sales
3	B	44	10		1	45	30		1	44	25
4	C	43	15		2	44	15		2	43	15
5	D	45	20
6	E	46	10
7	A	44	15
8	E	43	5
9	F	42	10
10	B	43	15
11	C	44	25
12	A	45	30
13	D	46	10
14
15
16
Sheet2

Cell Formulas
Range	Formula
F3:F4	F3	=AGGREGATE(14,6,$B$2:$B$13/($A$2:$A$13=$F$1),E3)
G3:G4	G3	=SUMIFS(C:C,A:A,$F$1,B:B,F3)
J3:J4	J3	=AGGREGATE(14,6,$B$2:$B$13/($A$2:$A$13=$J$1),I3)
K3:K4	K3	=SUMIFS(C:C,A:A,$J$1,B:B,J3)

 

[Trepidatioussaurus]

Hi Sufiyan,

Thanks for introducing me to the aggregate function! I'll use that more often... Unfortunately, my example wasn't the best. For instance, if I change the region for shoe size 46 - highlighted in yellow - to "A", I get 46 as top value, whilst it should be 45 still (top by sum of sales).

Also, how could this work with string values, e.g. every number is preceeded by letter "X"?

Mappe1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1					Region	A			Region	C
2
3	Region	Shoe Size	Sales		Top 2	Shoe Size	Sum Sales		Top 2	Shoe Size	Sum Sales
4	A	43	5		1	45	30		1	44	25
5	B	44	10		2	44	15		2	43	15
6	C	43	15			dynamic				dynamic
7	D	45	20
8	E	46	10
9	A	44	15		Region	A			Region	C
10	E	43	5
11	F	42	10		Top 2	Shoe Size	Sum Sales		Top 2	Shoe Size	Sum Sales
12	B	43	15		1	46	10		1	44	25
13	C	44	25		2	45	30		2	43	15
14	A	45	30
15	A	46	10
16
Tabelle1

Cell Formulas
Range	Formula
F12:F13	F12	=AGGREGATE(14,6,$B$4:$B$15/($A$4:$A$15=$F$9),E12)
G12:G13	G12	=SUMIFS(C:C,A:A,$F$9,B:B,F12)
J12:J13	J12	=AGGREGATE(14,6,$B$4:$B$15/($A$4:$A$15=$J$9),I12)
K12:K13	K12	=SUMIFS(C:C,A:A,$J$9,B:B,J12)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1					Region	A			Region	C
2
3	Region	Shoe Size	Sales		Top 2	Shoe Size	Sum Sales		Top 2	Shoe Size	Sum Sales
4	A	43	5		1	45	30		1	44	25
5	B	44	10		2	44	15		2	43	15
6	C	43	15
7	D	45	20
8	E	46	10
9	A	44	15
10	E	43	5
11	F	42	10
12	B	43	15
13	C	44	25
14	A	45	30
15	A	46	10
16
Main

Cell Formulas
Range	Formula
F4:F5	F4	=INDEX($B$4:$B$100,AGGREGATE(15,6,(ROW($B$4:$B$100)-ROW($B$4)+1)/($A$4:$A$100=$F$1)/($C$4:$C$100=G4),COUNTIFS(G$4:G4,G4)))
G4:G5	G4	=AGGREGATE(14,6,$C$4:$C$100/($A$4:$A$100=$F$1),ROWS(G$4:G4))
J4:J5	J4	=INDEX($B$4:$B$100,AGGREGATE(15,6,(ROW($B$4:$B$100)-ROW($B$4)+1)/($A$4:$A$100=$J$1)/($C$4:$C$100=K4),COUNTIFS(K$4:K4,K4)))
K4:K5	K4	=AGGREGATE(14,6,$C$4:$C$100/($A$4:$A$100=$J$1),ROWS(G$4:G4))

 

[Skybluekid]

HI Trepidatioussaurus

I think the reason why you are getting 46 instead of 45, is that on your original post, on the last row you had D in the region Column not A. So,Sufiyan97 solution is valid.

Try this with an "X" infront.

Book1
	B	C	D	E	F	G	H
2	A	X43	5			A
3	B	X44	10		1	45	30
4	C	X43	15		2	44	15
5	D	X45	20
6	E	X46	10
7	A	X44	15
8	E	X43	5
9	F	X42	10
10	B	X43	15
11	C	X44	25
12	A	X45	30
13	D	X46	10
Sheet1

Cell Formulas
Range	Formula
G3:G4	G3	=RIGHT(INDEX($C$2:$C$13,AGGREGATE(14,6,ROW($C$2:$C$13)-ROW($C$1)/($B$2:$B$13=$G$2),F3)),2)
H3	H3	=SUMPRODUCT(--(RIGHT(C2:C13,2)=G3)*($B$2:$B$13=$G$2)*(D2:D13))
H4	H4	=SUMPRODUCT(--(RIGHT(C2:C13,2)=G4)*($B$2:$B$13=$G$2)*(D2:D13))

 

[Fluff]

@Skybluekid I think that you are missing the point. It is the top 2 based on the sales NOT the shoe size.

 

[Fluff]

@Trepidatioussaurus forgot to mention, my solution doesn't care if column B is text or numbers.

 

[Trepidatioussaurus]

Hi Skybluekid and Fluff,

Thanks to both of you for your suggestions how to solve my problem. With the example I provided, Fluffs solution worked like a charm!
Unfortunately, my example was flawed in that it has unique values for any combination of region and shoe size. But what if there are several rows with identical regions and shoe sizes and sometimes even sales. That might be the case when adding a dimension like Month but I want to show Top list for the whole year.
I know this changes the demand but this is the hump I couldn't get over.

Mappe1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1						Region	A			Region	A
2										desired result
3	Month	Region	Shoe Size	Sales		Top 4	Shoe Size	Sum Sales		Top 4	Shoe Size	Sum Sales
4	Jan	A	X43	5		1	X45	30		1	X43	35
5	Feb	A	X44	15		2	X44	15		2	X45	30
6	Mar	A	X45	30		3	X43	15		3	X44	15
7	Mar	A	X43	15		4	X43	15		4	X46	10
8	Apr	A	X43	15
9	Mar	A	X46	10
10	Jan	B	X44	10
11	Feb	B	X43	15
12	Apr	C	X43	15
13	Mar	C	X44	25
14	Jan	D	X45	20
15	Feb	E	X46	10
16	Jan	E	X43	5
17	Feb	F	X42	10
Tabelle1

Cell Formulas
Range	Formula
G4:G7	G4	=INDEX($C$4:$C$102,AGGREGATE(15,6,(ROW($C$4:$C$102)-ROW($C$4)+1)/($B$4:$B$102=$G$1)/($D$4:$D$102=H4),COUNTIFS(H$4:H4,H4)))
H4:H7	H4	=AGGREGATE(14,6,$D$4:$D$102/($B$4:$B$102=$G$1),ROWS(H$4:H4))

 

[Fluff]

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),"")