SMALL function to show top 10 smallest values with their names

[Akbarov]

Hey community,

I need formula to show TOP 10 smallest values with their names ( Cell A1:A119 contains names of products and B1:B119 quantity of products )

Note: some products got same quantity

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	product	qty		top
2	p1	49		5
3	p3	29		6
4	p4	52		lowest scores	lowest top products
5	p7	64		29	p3
6	p2	50		30	p11
7	p09	59		49	p1
8	p12	54		49	p17
9	p20	52		50	p2
10	p11	30		50	p35
11	p17	49
12	p35	50
Sheet1

D2: 5 (Change to 10 for your data.)

In D3 just enter:

=COUNTIFS(B2:B12,"<="&SMALL(B2:B12,MIN(D2,COUNT(B2:B12))))

This adjusts Top N for ties.

In D5 just enter and copy down:

=IF(ROWS($D$5:D5)<=$D$3,SMALL($B$2:$B$12,ROWS($D$5:D5)),"")

In E5 control+shift+enter, not just enter, and copy down:

=IF($D5="","",INDEX($A$2:$A$12,SMALL(IF($B$2:$B$12=$D5,ROW($A$2:$A$12)-ROW($A$2)+1),COUNTIFS($D$5:D5,D5))))

 

[Akbarov]

dunno how but it Works thank you very much

 

[Aladin Akyurek]

dunno how but it Works thank you very much

You are welcome.