sumif large top 5 items

[whatever999]

Hi,

I have the following data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]State[/TD]
[TD]Customers[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NY[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NY[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]NY[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CA[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]WA[/TD]
[TD]900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]OR[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CA[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WA[/TD]
[TD]3000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CA[/TD]
[TD]2500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to rank top 3 cities by customers (regardless of day). Desired result:

Top 1: WA , 3900
Top 2: CA, 3700
Top 3: NY, 1800
Top 4: OR, 200

How can I do that with one formula?

thanks

 

[whatever999]

anyone? please

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]Day[/td][td]State[/td][td]Customers[/td][td][/td][td][/td][td]

5​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]NY[/td][td]

500​

[/td][td][/td][td][/td][td]

4​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

2​

[/td][td]NY[/td][td]

300​

[/td][td][/td][td][/td][td]state[/td][td]cus value[/td][/tr]
[tr][td]

4​

[/td][td]

3​

[/td][td]NY[/td][td]

1000​

[/td][td][/td][td][/td][td]WA[/td][td]

3900​

[/td][/tr]
[tr][td]

5​

[/td][td]

1​

[/td][td]CA[/td][td]

800​

[/td][td][/td][td][/td][td]CA[/td][td]

3700​

[/td][/tr]
[tr][td]

6​

[/td][td]

1​

[/td][td]WA[/td][td]

900​

[/td][td][/td][td][/td][td]NY[/td][td]

1800​

[/td][/tr]
[tr][td]

7​

[/td][td]

1​

[/td][td]OR[/td][td]

200​

[/td][td][/td][td][/td][td]OR[/td][td]

200​

[/td][/tr]
[tr][td]

8​

[/td][td]

2​

[/td][td]CA[/td][td]

400​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

2​

[/td][td]WA[/td][td]

3000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

3​

[/td][td]CA[/td][td]

2500​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

For the formulas to work, we need to install the V() function using Alt+F11 of which the code is as follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

F1 : 5 (desired top N)

In F2 control+shift+enter, not just enter:

=SUM(IF(V(SUMIFS($C$2:$C$10,$B$2:$B$10,IF(FREQUENCY(IF(1-($B$2:$B$10=""),MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1),$B$2:$B$10)))>=LARGE(V(),MIN(F1,SUM(IF(V()>0,1)))),1))

In F4 control+shift+enter, not just enter, and copy down:

=IF($G4="","",INDEX($B$2:$B$10,SMALL(IF(V(SUMIFS($C$2:$C$10,$B$2:$B$10,IF(FREQUENCY(IF(1-($B$2:$B$10=""),MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1),$B$2:$B$10)))=$G4,ROW($B$2:$B$10)-ROW($B$2)+1),COUNTIFS($G$4:G4,G4))))

In G4, control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$4:G4)>$F$2,"",LARGE(SUMIFS($C$2:$C$10,$B$2:$B$10,IF(FREQUENCY(IF(1-($B$2:$B$10=""),MATCH($B$2:$B$10,$B$2:$B$10,0)),ROW($B$2:$B$10)-ROW($B$2)+1),$B$2:$B$10)),ROWS($G$4:G4)))