Select Top 5 based on multiple criteria

[nikhil0311]

	A	B	C	D	E	F	G	H	I
Criteria

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Stage[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Country[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]$ Revenue[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Bid[/TD]
[TD="align: center"]India[/TD]
[TD="align: center"]$7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Bid[/TD]
[TD="align: center"]India[/TD]
[TD="align: center"]$5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Mandate[/TD]
[TD="align: center"]China[/TD]
[TD="align: center"]$4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Stage[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4F6228]#4F6228[/URL] , align: center"]Bid[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Bid[/TD]
[TD="align: center"]India[/TD]
[TD="align: center"]$6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Country[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4F6228]#4F6228[/URL] , align: center"]India[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Opp[/TD]
[TD="align: center"]HK[/TD]
[TD="align: center"]$2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]Bid[/TD]
[TD="align: center"]India[/TD]
[TD="align: center"]$4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Top 5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]$ Revenue[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]89[/TD]
[TD="align: center"]Mandate[/TD]
[TD="align: center"]China[/TD]
[TD="align: center"]$5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]opp[/TD]
[TD="align: center"]Aus/NZ[/TD]
[TD="align: center"]$6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]Opp[/TD]
[TD="align: center"]Aus/NZ[/TD]
[TD="align: center"]$7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]Bid[/TD]
[TD="align: center"]India[/TD]
[TD="align: center"]$8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]Opp[/TD]
[TD="align: center"]Malaysia[/TD]
[TD="align: center"]$9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]76[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]Bid[/TD]
[TD="align: center"]India[/TD]
[TD="align: center"]$10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

Hello Friends
I have a data from column A1:D13
based on column A i.e. ID AND column B i.e. stage AND column C i.e. Country
I want to sum Column D i.e. Revenue.

For instance if Id is 1 and Stage is Bid & Country is India then the revenue should be $12.
Now in Column G4:H5 I have criteria and based on this criteria i want Top 5 Revenues & ID's based on Revenue (largest to smallest)in Column H8:I13.
Let me know if this is possible or not?

Thanks in advance!!

 

[nikhil0311]

Hello. Please can anyone give it a shot?

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H
1	ID	Stage	Country	$ Revenue			Criteria
2	1	Bid	India	7			Stage	Bid
3	1	Bid	India	5			Country	India
4	2	Mandate	China	4			Top	5
5	4	Bid	India	6			Top 5 Value	4
6	5	Opp	HK	2			Top 5 Adjusted	5
7	76	Bid	India	4
8	89	Mandate	China	5			ID	$ Revenue
9	54	opp	Aus/NZ	6			1	12
10	33	Opp	Aus/NZ	7			33	10
11	45	Bid	India	8			45	8
12	43	Opp	Malaysia	9			4	6
13	33	Bid	India	10			76	4
14
Sheet1

1. H4 houses the desired Top N value.

2. In H5 control+shift+enter, not just enter:

=LARGE(SUMIFS(D2:D13,B2:B13,H2,C2:C13,H3,A2:A13,IF(FREQUENCY(IF(1-(A2:A13=""),IF(B2:B13=H2,IF(C2:C13=H3,MATCH(A2:A13,A2:A13,0)))),ROW(A2:A13)-ROW(A2)+1),A2:A13)),MIN(H4,SUM(IF(FREQUENCY(IF(1-(A2:A13=""),IF(B2:B13=H2,IF(C2:C13=H3,MATCH(A2:A13,A2:A13,0)))),ROW(A2:A13)-ROW(A2)+1),1))))

This cell calculates the Nth conditional sum...

3. In H6 control+shift+enter, not just enter:

=IFERROR(SUM(IF(SUMIFS(D2:D13,B2:B13,H2,C2:C13,H3,A2:A13,IF(FREQUENCY(IF(1-(A2:A13=""),IF(B2:B13=H2,IF(C2:C13=H3,MATCH(A2:A13,A2:A13,0)))),ROW(A2:A13)-ROW(A2)+1),A2:A13))>=H5,1)),0)

This cell re-calculates the Top N value based on the available data and the ties of the Nth value if any.

4. In G9 control+shift+enter, not just enter, and copy down in order to list the Top ID's:

=IF($H9="","",INDEX($A$2:$A$13,SMALL(IF(SUMIFS($D$2:$D$13,$B$2:$B$13,$H$2,$C$2:$C$13,$H$3,$A$2:$A$13,IF(FREQUENCY(IF(1-($A$2:$A$13=""),IF($B$2:$B$13=$H$2,IF($C$2:$C$13=$H$3,MATCH($A$2:$A$13,$A$2:$A$13,0)))),ROW($A$2:$A$13)-ROW($A$2)+1),$A$2:$A$13))=$H9,ROW($A$2:$A$13)-ROW($A$2)+1),COUNTIFS($H$9:H9,H9))))

5. In H9 control+shift+enter, not just enter, and copy down in order to list the Top revenues:

=IF(ROWS($H$9:H9)>$H$6,"",LARGE(SUMIFS($D$2:$D$13,$B$2:$B$13,$H$2,$C$2:$C$13,$H$3,$A$2:$A$13,IF(FREQUENCY(IF(1-($A$2:$A$13=""),IF($B$2:$B$13=$H$2,IF($C$2:$C$13=$H$3,MATCH($A$2:$A$13,$A$2:$A$13,0)))),ROW($A$2:$A$13)-ROW($A$2)+1),$A$2:$A$13)),ROWS($H$9:H9)))

 

[nikhil0311]

Thanks a Ton Aladin.

 

[Aladin Akyurek]

Thanks a Ton Aladin.

You are welcome.