5 highest values with multiple criterias and possible tie

[wixie]

Hi, looked for forum to find answer for my question but didn't manage to find solution. I would like to find 5 top values from data, based on certain criteria. I also need way to present results, if there is a tie between 2 values, when status is same for both (for example companies: Foxtrot and India).
I need to export the data from excel to some other softwares, so visual sorting isnt option. I think that the functions for: F3 and G3 are the most important (that's why i blurred the lats 3 columns of this example).

I have tried the LARGE and SMALL functions, but my brains just explodes...

Here is an example to descibre my problem in a case that Deal status is O:

	A	B	C	D	E	F	G	H	I	J	K
1	Value	Company	Status		Deals Open				Deals Lost
2	123 456	Alfa	Lost		No:	Value	Company		No:	Value	Company
3	11 000	Beta	Won		1.				1
4	12 500	Charlie	Open		2.				2.
5	123 456	Delta	Lost		3.				3.
6	200 050	Foxtrot	Open		4.				4.
7	290 321	Golf	Open		5.				5.
8	123 543	Hotel	Won
9	200 050	India	Open
10	125 076	Juliett	Won

<tbody>
</tbody>

 

[Aladin Akyurek]

My office is in finnish but before my attempt to fix the formula, i get "missing"-note (PUUTTUU in finnish). when i try to fix the code, i get the popup that tells that there is error in this formula and = 1+1 should be 2, and it advices me to check the formula. And yes, i pressed CTRL+SHIFT+ENTER

If you open the file I provided, you'll get everything in Finnish.

In fact, you don't have to change anything to this formula:

=IF($F3="","",INDEX($B$2:$B$10,SMALL(IF($C$2:$C$10="open",IF($A$2:$A$10=$F3,ROW($B$2:$B$10)-ROW($B$2)+1)),COUNTIFS($F$3:F3,F3))))

unless INDEX comes with something you don't want or expect.

 

[wixie]

Ok, hmm i assume that there is then something else wrong.. got to check my formula again.. thanks..

 

[wixie]

Yup, found a typo, feel bit ashamed

 

[Aladin Akyurek]

Yup, found a typo, feel bit ashamed

Don't. Glad to see it's sorted out.