Top 10 Results based on sum

[Biplab1985]

Hi,

I have 3 columns Customer, Brand and volume. On selection of customer in cell A1, top 10 brands should populate high to lowest volume of brands in that Customer

I tried to use Index and Match but it do not give any result

any help

Thanks!

[modytrane]

can you attach a sample file and explain the results expected? It's hard to understand exactly what you need.

[Syrkrasi]

use a pivot table then Value Filter by Top 10

[Biplab1985]

I am Aware by Pivot but cannot use it as this is in result section. A formula or VBA will help.

Sample file attached and desired result

[JohnTopley]

Try

in I3

=LARGE(IF($C$2:$C$30=$G$3,$D$2:$D$30),ROWS($1:1))

in H3

=INDEX($A$2:$A$30,MATCH($G$3&$I3,$C$2:$C$30&$D$2:$D$30,0))

BOTH ..

...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.

Copy down

[Biplab1985]

That worked perfectly.

Is there a way i could use this through VBA using a worksheet change function?

[Biplab1985]

Hi,

The solution given above works fine but I wanted the highest to lowest Problem Area based on total volume of Problem Area and not large of region.

So basically the top 10 Problem area Should be calculated on highest total volume of Problem Area in that Region. In this case the top Problem Area should be UHHT for total volume of 230 in Midwest Region

[JohnTopley]

The data only has one entry for UHHT and it is 50!

The formula finds the largest VOLUME for a selected region.

[Biplab1985]

UHHT has two entry in Midwest 50 and 180, the formula should find the largest total volume. I tried something like this but no luck
=INDEX($A$2:$A$31,MATCH($G$3,LARGE(SUMIFS(D2:D31,C2:C31,G3,A2:A31,A2:A31),$C$2:$C$31&$D$2:$D$31),0))
By pressing CTRL+SHIFT+ENTER

[Biplab1985]

Any input?

All I need is highest total volume of Problem Area in the Region to be used to determine the top 10 result not one single value

[JohnTopley]

Helper in F

in F2

=IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)=1,SUMIFS(D:D,C:C,$G$3,A:A,A2),"")

in I3

=LARGE(IF($C$2:$C$30=$G$3,$F$2:$F$30),ROWS($1:1))

in H3

=INDEX($A$2:$A$30,MATCH($G$3&$I3,$C$2:$C$30&$F$2:$F$30,0))

As before both array entered (CSE)

[Biplab1985]

Worked perfectly, Thank you so much