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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Value[/TD]
[TD]Company[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Deals Open[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Deals Lost
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123 456[/TD]
[TD]Alfa[/TD]
[TD]Lost[/TD]
[TD][/TD]
[TD]No:[/TD]
[TD]Value[/TD]
[TD]Company[/TD]
[TD][/TD]
[TD]No:
[/TD]
[TD]Value
[/TD]
[TD]Company
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11 000[/TD]
[TD]Beta[/TD]
[TD]Won[/TD]
[TD][/TD]
[TD]1.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12 500[/TD]
[TD]Charlie[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]2.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2.
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]123 456[/TD]
[TD]Delta[/TD]
[TD]Lost[/TD]
[TD][/TD]
[TD]3.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3.
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]200 050[/TD]
[TD]Foxtrot[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]4.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4.
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]290 321[/TD]
[TD]Golf[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD]5.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5.
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]123 543[/TD]
[TD]Hotel[/TD]
[TD]Won[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]200 050[/TD]
[TD]India[/TD]
[TD]Open[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]125 076[/TD]
[TD]Juliett[/TD]
[TD]Won[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.