Top 3 items based on criteria

Rolly_Sefu

Board Regular
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>

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Special-K99

Well-known Member
1. "based on the criteria 2."

What does criteria 2 mean ?

2. How do you get 5 apples ?

Or 2 oranges for that matter ?

Last edited:

Rolly_Sefu

Board Regular
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

Well-known Member
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.

Last edited:

Special-K99

Well-known Member

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

Board Regular
#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

Board Regular

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
"

Last edited:

Special-K99

Well-known Member
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

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

Code:
``=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

Code:
``=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

Last edited:

Rolly_Sefu

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

Thanks

Replies
1
Views
29
Replies
8
Views
69
Replies
8
Views
425
Replies
3
Views
62
Replies
5
Views
185