Help with excel filter formula

[manekankit]

Dear members, having below query with excel filter function.

Given below is data set, formula 1 and formula 2.

Having query with formula 2 given below.

I am not getting why result of formula 2 is including item with units sold =4 when my condition for filter is unit sold is >5. (formula 2 is also not including units sold =7 item).

I am using office 365 desktop version.

Thanks.

A 1 Employee B Units Sold C Average Price 2 Tom 4​ 2.99​ 3 Jack 3​ 2.95​ 4 Suzie 7​ 2.75​ 5 Rajesh 4​ 2.64​ 6 Kevin 4​ 3.05​ 7 Belinda 3​ 2.52​ 8 Sally 8​ 3.18​ 9 David 9​ 2.52​	This is a data set range (a1 to c9)
Result of formula 1 Suzie 7​ 2.75​ Sally 8​ 3.18​ David 9​ 2.52​	Formula 1 =FILTER(SORTBY(A2:C9,B2:B9,1),SORTBY(B2:B9,B2:B9,1)>5)	This formula is giving correct result providing list where unit sold quantity is >5
Result of formula 2 Tom 4​ 2.99​ Sally 8​ 3.18​ David 9​ 2.52​	Formula 2 =FILTER(SORTBY(A2:C9,B2:B9,1),B2:B9>5)	The query is why this formula is including "Tom" when it has unit sold = 5 but filter condition is >5

 

[Fluff]

Try it like

=sort(FILTER(A2:C9,B2:B9>5),2)

 

[JamesCanale]

filter first, then sort.

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H	I
1	Emp	Units	Price
2	Tom	4	2.99
3	Jack	3	2.95
4	Suzie	7	2.75
5	Rajesh	4	2.64
6	Kevin	4	3.05
7	Belinda	3	2.52
8	Sally	8	3.18
9	David	9	2.52
10
11					sortby output				filter output
12	Tom	4	2.99		Jack	3	2.95		FALSE
13	Sally	8	3.18		Belinda	3	2.52		FALSE
14	David	9	2.52		Tom	4	2.99		TRUE
15					Rajesh	4	2.64		FALSE
16					Kevin	4	3.05		FALSE
17					Suzie	7	2.75		FALSE
18					Sally	8	3.18		TRUE
19					David	9	2.52		TRUE
20	try this:
21	Suzie	7	2.75
22	Sally	8	3.18
23	David	9	2.52
Sheet33

Cell Formulas
Range	Formula
A12:C14	A12	=FILTER(SORTBY(A2:C9,B2:B9,1),B2:B9>5)
E12:G19	E12	=SORTBY(A2:C9,B2:B9,1)
I12:I19	I12	=B2:B9>5
A21:C23	A21	=SORT(FILTER(A2:C9,B2:B9>5),2)
Dynamic array formulas.

 

[Joe4]

I am using office 365 desktop version.

Please update your Profile then, as Excel 2016 does not have the Filter function.

 

[manekankit]

Please update your Profile then, as Excel 2016 does not have the Filter function.

View attachment 97371

Updated. Sorry for inconvenience.

 

[manekankit]

Thanks @Fluff and @JamesCanale for replying. I am still not clear why my second formula is not giving desired output. Could you please explain what's wrong with the second formula? If we see, it is in a way similar to the first formula which is working as expected.

 

[Joe4]

Updated. Sorry for inconvenience.

No worries. It just helps let us know which version you are using, so we know which functions will and will not work for you (and we won't recommend a solution that you cannot use).

 

[Fluff]

In your 1st formula you are sorting the data & the criteria & then filtering.
In the 2nd you are sorting the data, but not the criteria, therefore they do not line up

In E2 is the result of the sort & the green lines are those that match your criteria, so that is what is returned.

Fluff.xlsm
	A	B	C	D	E	F	G
1	Emp	Units	Price
2	Tom	4	2.99		Jack	3	2.95
3	Jack	3	2.95		Belinda	3	2.52
4	Suzie	7	2.75		Tom	4	2.99
5	Rajesh	4	2.64		Rajesh	4	2.64
6	Kevin	4	3.05		Kevin	4	3.05
7	Belinda	3	2.52		Suzie	7	2.75
8	Sally	8	3.18		Sally	8	3.18
9	David	9	2.52		David	9	2.52
Master

Cell Formulas
Range	Formula
E2:G9	E2	=SORTBY(A2:C9,B2:B9,1)
Dynamic array formulas.

 

[manekankit]

In your 1st formula you are sorting the data & the criteria & then filtering.
In the 2nd you are sorting the data, but not the criteria, therefore they do not line up

In E2 is the result of the sort & the green lines are those that match your criteria, so that is what is returned.

Fluff.xlsm
	A	B	C	D	E	F	G
1	Emp	Units	Price
2	Tom	4	2.99		Jack	3	2.95
3	Jack	3	2.95		Belinda	3	2.52
4	Suzie	7	2.75		Tom	4	2.99
5	Rajesh	4	2.64		Rajesh	4	2.64
6	Kevin	4	3.05		Kevin	4	3.05
7	Belinda	3	2.52		Suzie	7	2.75
8	Sally	8	3.18		Sally	8	3.18
9	David	9	2.52		David	9	2.52
Master

Cell Formulas
Range	Formula
E2:G9	E2	=SORTBY(A2:C9,B2:B9,1)
Dynamic array formulas.

Thank you.

 

[manekankit]

th

filter first, then sort.

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H	I
1	Emp	Units	Price
2	Tom	4	2.99
3	Jack	3	2.95
4	Suzie	7	2.75
5	Rajesh	4	2.64
6	Kevin	4	3.05
7	Belinda	3	2.52
8	Sally	8	3.18
9	David	9	2.52
10
11					sortby output				filter output
12	Tom	4	2.99		Jack	3	2.95		FALSE
13	Sally	8	3.18		Belinda	3	2.52		FALSE
14	David	9	2.52		Tom	4	2.99		TRUE
15					Rajesh	4	2.64		FALSE
16					Kevin	4	3.05		FALSE
17					Suzie	7	2.75		FALSE
18					Sally	8	3.18		TRUE
19					David	9	2.52		TRUE
20	try this:
21	Suzie	7	2.75
22	Sally	8	3.18
23	David	9	2.52
Sheet33

Cell Formulas
Range	Formula
A12:C14	A12	=FILTER(SORTBY(A2:C9,B2:B9,1),B2:B9>5)
E12:G19	E12	=SORTBY(A2:C9,B2:B9,1)
I12:I19	I12	=B2:B9>5
A21:C23	A21	=SORT(FILTER(A2:C9,B2:B9>5),2)
Dynamic array formulas.

thank you. this also explains in a simplified way.