Select Top 5 based on multiple criteria

[nikhil0311]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.