sumif large top 5 items

[whatever999]

Hi,

I have the following data:

Day	State	Customers
1	NY	500
2	NY	300
3	NY	1000
1	CA	800
1	WA	900
1	OR	200
2	CA	400
2	WA	3000
3	CA	2500

<tbody>
</tbody>

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]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Day	State	Customers			5​
2​	1​	NY	500​			4​
3​	2​	NY	300​			state	cus value
4​	3​	NY	1000​			WA	3900​
5​	1​	CA	800​			CA	3700​
6​	1​	WA	900​			NY	1800​
7​	1​	OR	200​			OR	200​
8​	2​	CA	400​
9​	2​	WA	3000​
10​	3​	CA	2500​

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