Additional criteria in FILTER formula

[ozil]

Hi
I’m using filter function and its working but now I’m thinking how to get returned column 2 as per formula for values >800?

So here is formula. And the last two 6 is returned column and -1 sorted ascending but i would see only over 800

=SORT(CHOOSECOLS(FILTER(DATA[[Region]:[WTD 3 Average]],DATA[Region]=Sheet1!N7),1,2,3,4,5,19),6,-1)

 

[ozil]

How do i copy this into excel now ? So what I would need is filtering as you done I think, and two drop down list for filtering one for months and see values for every region and one for regions so i can see all regions or only selected in region2 and filter by months? And values only over >$600
As my spreadsheet is for about 400 rows. Not sire if explained well.

 

[ozil]

thank you. how do i copy this now into excel ?
so what i would need is the filtering as you done? and then last column return depend of 2 drop down lists. one for month OT Cost, and one for regions when regions not selected then see all regions. and OT Cost values over >600. my spreadsheet has about 360 rows to filter.

 

[JamesCanale]

Maybe:

MrExcelPlayground24.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	Region	Region2	HUB	Employee Name	Role	Contract	30-Dec	OT 30-Dec	6-Jan	OT 06-Jan	13-Jan	OT 13-Jan	20-Jan	OT 20-Jan	27-Jan	OT 27-Jan	Jan Total Hours	Jan Average Hours	Jan Total OT Hours	Jan OT Cost	Column7	3-Feb	OT 03-Feb	10-Feb	OT 10-Feb	17-Feb	OT 17-Feb	24-Feb	OT 24-Feb	Feb Total Hours	Feb Average Hours	Feb Total OT Hours	Feb OT Cost	Column16	3-Mar	OT 03-Mar	10-Mar	OT 10-Mar	17-Mar	OT 17-Mar	24-Mar	OT 24-Mar	Mar Total Hours	Mar Average Hours	Mar Total OT Hours	Mar OT Cost
2	South	South Central	BBB	ooooo	WRH	40	40	0	42.75	2.75	46	6	42	2	46	6	216.75	43.35	10.75	700		44	4	42.75	2.75	41.5	1.5	39.75	0	168	42	14.25	400		40.5	0.5	40	0	41	1		0	121.5	40.5	1.5	24.375
3	South	South West	AAA	gggggh	WRH	40	42	2	44	4	43.75	3.75	41.25	1.25	42.25	2.25	213.25	42.65	11	500		42.5	2.5	40.25	0.25	41.5	1.5	45.25	5.25	169.5	42.375	6.5	500		40	0	40.25	0.25	40	0		0	120.25	40.08333	5.5	89.375
4	South	South East	CCC	fshj	LCV	48	48	0	42	0	39.5	0	37.5	0	41.5	0	208.5	41.7	0	900		42.5	0	42.5	0	42.5	0	42.5	0	170	42.5	0	700		48	0	45	0	42.5	0		0	135.5	45.16667	0	0
5	West	West1	BBB	ooooo	WRH	40	40	0	42.75	2.75	46	6	42	2	46	6	216.75	43.35	10.75	700		44	4	42.75	2.75	41.5	1.5	39.75	0	168	42	14.25	400
6	West	West2Theoden Hal	AAA	gggggh	WRH	40	42	2	44	4	43.75	3.75	41.25	1.25	42.25	2.25	213.25	42.65	11	500		42.5	2.5	40.25	0.25	41.5	1.5	45.25	5.25	169.5	42.375	6.5	500
7	West	West 3	CCC	fshj	LCV	48	48	0	42	0	39.5	0	37.5	0	41.5	0	208.5	41.7	0	900		42.5	0	42.5	0	42.5	0	42.5	0	170	42.5	0	700
8
9
10				Drop Down for months ->	Jan	South	20						Jan	20		North
11													Feb	33		South
12	Region	Region2	HUB	Employee Name	Role	Contract	Cost						Mar	46		East
13	South	South Central	BBB	ooooo	WRH	40	700						Apr	61		West
14	South	South East	CCC	fshj	LCV	48	900						May	77
15													Jun	90
16													Jul	105
17													Aug	118
18													Sep	134
19													Oct	149
20													Nov	162
21													Dec	175
Sheet11

Cell Formulas
Range	Formula
G10	G10	=XLOOKUP(E10,M10:M21,N10#,"",0)
N10:N21	N10	=XMATCH(M10:M21&" OT Cost",1:1,0)
A13:G14	A13	=LET(a,A2:F7,b,OFFSET(A2,0,G10-1,ROWS(a),1),c,HSTACK(a,b),d,FILTER(c,(CHOOSECOLS(c,1)=F10)*(CHOOSECOLS(c,7)>600)),d)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E10	List	=$M$10:$M$21
F10	List	=$P$10:$P$13

 

[ozil]

Maybe:

when copy sheet to my excel just getting #value! #Spill!

 

[JamesCanale]

The formula I have in A13 spills out - 7 columns and as many rows as the filter allows for - so maybe a lot of rows. So if there is anything in that area of the spreadsheet - it will through that error. Remove what you can in that area.

 

[ozil]

it works. but when copied formula
=LET(a,DATA!A5:F362,b,OFFSET(DATA!B5,0,G3-1,ROWS(a),1),c,HSTACK(a,b),d,FILTER(c,(CHOOSECOLS(c,1)=F3)*(CHOOSECOLS(c,7)>500)),d)

to my data it gives #calc!

 

[JamesCanale]

Maybe try:
=LET(a,DATA!A5:F362,b,OFFSET(DATA!A5...
I guess that F3 is the dropdown for the region.

If that doesn't work, try taking apart the LET statement, meaning, instead of 'd' at the end, look at c, then b, then a, to see where the error is.

 

[ozil]

sorry sorted just pick up wrong cell. anyway can you edit now to sort values descending?

 

[ozil]

sorry sorted just pick up wrong cell. anyway can you edit now to sort values descending?

and can you add OT total hours for each month as well in column before cost? and sort cost ascending.

 

[ozil]

and can you add OT total hours for each month as well in column before cost? and sort cost ascending.

sorted sorting descending. but can you add please ot total hours for each month with cost?