Top 3 items based on criteria

[Rolly_Sefu]

Hello

I want to know how can I get a TOP 3 for column FRUIT for the month JAN based on the criteria 2.

Top 1 : Apple : 5
Top 2 : Orange : 2
Top 3 : Banana : 1

I have the following table:

[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]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Region[/TD]
[TD]Fruit[/TD]
[TD]WK1[/TD]
[TD]WK2[/TD]
[TD]WK3[/TD]
[TD]WK4[/TD]
[TD]WK5[/TD]
[TD]WK6[/TD]
[TD]WK7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Region1[/TD]
[TD]apple[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Region1[/TD]
[TD]banana[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]region1[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]region2[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]region3[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]region3[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]region3[/TD]
[TD]orange[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

1. "based on the criteria 2."

What does criteria 2 mean ?

2. How do you get 5 apples ?

Or 2 oranges for that matter ?

 

[Rolly_Sefu]

Hy, sorry, Criteria: "2" , the 2 is a value in the cell

Apple is TOP 1 for Jan => sum all 2's in WK1 to WK4 and you get 5.

 

[Special-K99]

For apples: If you sum all 2s in WK1 to WK5 you get 10 2+2+2+2+2

If however you COUNT the number of 2s for apples you get 5.

 

[Special-K99]

Here's a start

in J3
=SUMPRODUCT((B$3:B$9=J3)*(C$3:I$9=2))
Array formula, use Ctrl-Shift-Enter
copy down for as many rows as you have in column B

in K3
=SUMPRODUCT((B$3:B$9=J3)*(C$3:I$9=2))
copy down for as many rows as you have in column B

 

[Rolly_Sefu]

#4 : Yes, sorry I was thinking about count.

#5 : the problem is that I need to TOP 3, but I only have the data. I have to find out the top 3 value and the names for the values.

The example I posted is short but my list is much larger.

 

[Rolly_Sefu]

If I do this: "in J3 =SUMPRODUCT((B$3:B$9=J3)*(C$3:I$9=2)) " I get a circular formula error, since I put in in J3

From #1 the top 3 is the desired result.

"
Top 1 : Apple : 5
Top 2 : Orange : 2
Top 3 : Banana : 1
"

 

[Special-K99]

Oops!

in J3
=IFERROR(INDEX($B$3:$B$9,SMALL(IF((COUNTIF(B$3:B3,B3)=1),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),1),"")
Array formula, use Ctrl-Shift-Enter

Not sure how to develop this further.

What happens if you get a tie ? What result would you expect then if apples and oranges were the same total ?

 

[Rolly_Sefu]

I managed to create a formula but not working as I would like it to:

=INDEX($B$3:$B$9;SMALL(IF(N(IF(1;IFERROR(MATCH(IF($C$3:$F$9=2;$B$3:$B$9;0);$B$3:$B$9;0);FALSE);0))=0;"";N(IF(1;IFERROR(MATCH(IF($C$3:$F$9=2;$B$3:$B$9;0);$B$3:$B$9;0);FALSE);0)));ROW(A1)))

'I get all the result but not in top order and they are multiplied, as you copy down

=INDEX($B$3:$B$9;MATCH(1;INDEX(($D$3:$D$9=LARGE(IF($D$3:$D$9=2;$D$3:$D$9;0);ROWS($H$12:H12)))*(COUNTIF($H$12:H12;$B$3:$B$9)=0););0))

'these are not in order, and I still need the number, and is gives an error when I try to add column ( since jan has more columns)

#8 : if I get a tie
Top 1 apples: 2
Top 2 oranges: 2
is OK

 

[Rolly_Sefu]

Does any one have any other ideas, how I could finish this ?

Thanks