Filter with Sort and multiple criteria.

[jd yort]

Hi everyone,

I have a minor hurdle that I have been slamming my hand in the desk drawer over and hoping an Excel Yoda can assist....
I want to Filter the below table and return the records based on the number sold for a region.

I can get it to work without restricting the region (formula in W3), however when I want to factor in the Region I can't quite get it to work (formula in W13).
I thought I would need to do it with an INDEX but maybe that is where I am going wrong and overly complicating it.

Any idea's legends?

Thanks

JD

=INDEX(SORT(FILTER($H$5:$K$18,($K$5:$K$18>=LARGE($K$5:$K$18,$K$2))*($J$5:$J$18=$I$2)),4,-1),,1)

 

[hagia_sofia]

Hello, would this work for you (are you using Excel 365?):

=SORT(FILTER(H5:K18,(J5:J18=I2)*((COUNTIFS(J5:J18,J5:J18,K5:K18,">"&K5:K18)+1)<=K2)),4,-1)

 

[StephenCrump]

If you are happy to show only five results, a shorter alternative would be: =TAKE(SORT(FILTER(H5:K18,J5:J18=I2),4,-1),K2)

If there are ties, @hagia_sofia's formula will include the tied values, and hence may show more than five results, which I think is the better outcome.

 

[Mr_Kay]

Hello, would this work for you (are you using Excel 365?):

=SORT(FILTER(H5:K18,(J5:J18=I2)*((COUNTIFS(J5:J18,J5:J18,K5:K18,">"&K5:K18)+1)<=K2)),4,-1)

I tried this and it #CALC! errors. This is on office 365

Proving Grounds.xlsx
	H	I	J	K	L	M	N	O	P	Q
2	Region:	Northern	Top:	5
3
4	Code	Business	Region	Sold			A1242	Secret Valley	Central	222
5	A1234	Golden City	Eastern	37			A1241	Rocky Mountain	Central	139
6	A1235	Beach River	Eastern	24			A1240	Lake View	Central	139
7	A1236	Green Fields	Nothern	178			A1236	Green Fields	Nothern	178
8	A1237	Shotgun Valley	Central	49			A1245	Edinsborough	Central	178
9	A1238	Mudsville	Nothern	24			A1244	Central City	Central	139
10	A1239	Smithfield	Central	67
11	A1240	Lake View	Central	139
12	A1241	Rocky Mountain	Central	139
13	A1242	Secret Valley	Central	222
14	A1243	Yabby Creek	Nothern	49			#CALC!
15	A1244	Central City	Central	139
16	A1245	Edinsborough	Central	178
17	A1246	Summer Bay	Central	24
18	A1247	Yabbie Creek	Eastern	37
Filter&Sort

Cell Formulas
Range	Formula
N4:Q9	N4	=SORT(FILTER(Table1, Table1[Sold]>=LARGE(Table1[Sold], K2)), 2, -1)
N14	N14	=SORT(FILTER(H5:K18,(J5:J18=I2)*((COUNTIFS(J5:J18,J5:J18,K5:K18,">"&K5:K18)+1)<=K2)),4,-1)
Dynamic array formulas.

 

[StephenCrump]

I tried this and it #CALC! errors

Perhaps if you spelled Northern correctly?

 

[jd yort]

Hello, would this work for you (are you using Excel 365?):

=SORT(FILTER(H5:K18,(J5:J18=I2)*((COUNTIFS(J5:J18,J5:J18,K5:K18,">"&K5:K18)+1)<=K2)),4,-1)

This is perfect. Exactly what I was after. Thank you so much and everyone else for taking the time to reply.

 

[jd yort]

Actually one follow up question... if I just wanted to only return the first column, ie the code only rather than the entire table.
What would I need to change?

=SORT(FILTER(H5:K18,(J5:J18=I2)*((COUNTIFS(J5:J18,J5:J18,K5:K18,">"&K5:K18)+1)<=K2)),4,-1)

I know it is not as simple at moving it from H5:K18 to H5:H18 in the Filter.

 

[hagia_sofia]

Many thanks for the feedback. As far as your question is concerned, actually it is just what you described.

 

[jd yort]

I was hoping that it was that simple but something is a miss.

=SORT(FILTER(H5:H18,(J5:J18=I2)*((COUNTIFS(J5:J18,J5:J18,K5:K18,">"&K5:K18)+1)<=K2)),4,-1)

 

[hagia_sofia]

Sorry, I forgot that the original formula was sorting the result by the 4th column - change the 4 at the end to 1 and it should work (you might also wanna check if -1 is the right order for you).