# Dynamic Top N List with SUMIFS



## Trepidatioussaurus (Jan 4, 2023)

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 (Jan 4, 2023)

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 (Jan 4, 2023)

Hi & Welcome to MrExcel

Try

Book2ABCDEFGHIJKL1ReegionShoe SizeSalesRegionARegionC2A435Top 2Shoe SizeSum SalesTop 2Shoe SizeSum Sales3B441014530144254C431524415243155D45206E46107A44158E4359F421010B431511C442512A453013D4610141516Sheet2Cell FormulasRangeFormulaF3:F4F3=AGGREGATE(14,6,$B$2:$B$13/($A$2:$A$13=$F$1),E3)G3:G4G3=SUMIFS(C:C,A:A,$F$1,B:B,F3)J3:J4J3=AGGREGATE(14,6,$B$2:$B$13/($A$2:$A$13=$J$1),I3)K3:K4K3=SUMIFS(C:C,A:A,$J$1,B:B,J3)


----------



## Trepidatioussaurus (Jan 5, 2023)

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.xlsxABCDEFGHIJK1RegionARegionC23RegionShoe SizeSalesTop 2Shoe SizeSum SalesTop 2Shoe SizeSum Sales4A43514530144255B441024415243156C4315dynamicdynamic7D45208E46109A4415RegionARegionC10E43511F4210Top 2Shoe SizeSum SalesTop 2Shoe SizeSum Sales12B4315146101442513C4425245302431514A453015A461016Tabelle1Cell FormulasRangeFormulaF12:F13F12=AGGREGATE(14,6,$B$4:$B$15/($A$4:$A$15=$F$9),E12)G12:G13G12=SUMIFS(C:C,A:A,$F$9,B:B,F12)J12:J13J12=AGGREGATE(14,6,$B$4:$B$15/($A$4:$A$15=$J$9),I12)K12:K13K12=SUMIFS(C:C,A:A,$J$9,B:B,J12)


----------



## Fluff (Jan 5, 2023)

How about
Fluff.xlsmABCDEFGHIJK1RegionARegionC23RegionShoe SizeSalesTop 2Shoe SizeSum SalesTop 2Shoe SizeSum Sales4A43514530144255B441024415243156C43157D45208E46109A441510E43511F421012B431513C442514A453015A461016MainCell FormulasRangeFormulaF4:F5F4=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:G5G4=AGGREGATE(14,6,$C$4:$C$100/($A$4:$A$100=$F$1),ROWS(G$4:G4))J4:J5J4=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:K5K4=AGGREGATE(14,6,$C$4:$C$100/($A$4:$A$100=$J$1),ROWS(G$4:G4))


----------



## Skybluekid (Jan 5, 2023)

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.
Book1BCDEFGH2AX435A3BX4410145304CX4315244155DX45206EX46107AX44158EX4359FX421010BX431511CX442512AX453013DX4610Sheet1Cell FormulasRangeFormulaG3:G4G3=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)H3H3=SUMPRODUCT(--(RIGHT(C2:C13,2)=G3)*($B$2:$B$13=$G$2)*(D2:D13))H4H4=SUMPRODUCT(--(RIGHT(C2:C13,2)=G4)*($B$2:$B$13=$G$2)*(D2:D13))


----------



## Fluff (Jan 5, 2023)

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


----------



## Fluff (Jan 5, 2023)

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


----------



## Trepidatioussaurus (Tuesday at 4:15 AM)

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.xlsxABCDEFGHIJKL1RegionARegionA2desired result3MonthRegionShoe SizeSalesTop 4Shoe SizeSum SalesTop 4Shoe SizeSum Sales4JanAX4351X45301X43355FebAX44152X44152X45306MarAX45303X43153X44157MarAX43154X43154X46108AprAX43159MarAX461010JanBX441011FebBX431512AprCX431513MarCX442514JanDX452015FebEX461016JanEX43517FebFX4210Tabelle1Cell FormulasRangeFormulaG4:G7G4=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:H7H4=AGGREGATE(14,6,$D$4:$D$102/($B$4:$B$102=$G$1),ROWS(H$4:H4))


----------



## Fluff (Tuesday at 6:09 AM)

How about
Fluff.xlsmABCDEFGHI1RegionA23MonthRegionShoe SizeSalesTop 4Shoe SizeSum Sales4JanAX435351X43355FebAX4415152X45306MarAX4530303X44157MarAX4315 4X46108AprAX4315 9MarAX46101010JanBX44101011FebBX43151512AprCX43151513MarCX44252514JanDX45202015FebEX46101016JanEX435517FebFX42101018MainCell FormulasRangeFormulaH4:H7H4=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:I7I4=AGGREGATE(14,6,$E$4:$E$102/($B$4:$B$102=$H$1),ROWS(I$4:I4))E4:E17E4=IF(COUNTIFS(C$4:C4,C4,B$4:B4,B4)=1,SUMIFS(D:D,B:B,B4,C:C,C4),"")


----------



## Trepidatioussaurus (Jan 4, 2023)

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 (Yesterday at 7:56 AM)

Fluff said:


> How about
> Fluff.xlsmABCDEFGHI1RegionA23MonthRegionShoe SizeSalesTop 4Shoe SizeSum Sales4JanAX435351X43355FebAX4415152X45306MarAX4530303X44157MarAX4315 4X46108AprAX4315 9MarAX46101010JanBX44101011FebBX43151512AprCX43151513MarCX44252514JanDX45202015FebEX46101016JanEX435517FebFX42101018MainCell FormulasRangeFormulaH4:H7H4=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:I7I4=AGGREGATE(14,6,$E$4:$E$102/($B$4:$B$102=$H$1),ROWS(I$4:I4))E4:E17E4=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 (Yesterday at 7:57 AM)

You're welcome & thanks for the feedback


----------

