Top 3 items based on criteria

Rolly_Sefu

Board Regular
Joined
Oct 25, 2013
Messages
137
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:

ABCDEFGHI
1JanJanJanJanFebFebFeb
2RegionFruitWK1WK2WK3WK4WK5WK6WK7
3Region1apple222
4Region1banana1
5region1orange2
6region2apple2
7region3apple2
8region3banana2
9region3orange12

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Oct 25, 2013
Messages
137
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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Nov 7, 2006
Messages
8,350

ADVERTISEMENT

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
Joined
Oct 25, 2013
Messages
137
#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
Joined
Oct 25, 2013
Messages
137

ADVERTISEMENT

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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Oct 25, 2013
Messages
137
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,308
Messages
5,527,930
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top