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:

	A	B	C	D	E	F	G	H	I
1			Jan	Jan	Jan	Jan	Feb	Feb	Feb
2	Region	Fruit	WK1	WK2	WK3	WK4	WK5	WK6	WK7
3	Region1	apple	2	2	2
4	Region1	banana	1
5	region1	orange		2
6	region2	apple				2
7	region3	apple		2
8	region3	banana			2
9	region3	orange	1	2

<tbody>
</tbody>

 

[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