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)

 

[JamesCanale]

This?? Adding a second filter where column 2 is greater than 800.

=SORT(CHOOSECOLS(FILTER(DATA[[Region]:[WTD 3 Average]],(DATA[Region]=Sheet1!N7)*(DATA[whatever column 2 is called]>800)),1,2,3,4,5,19),6,-1)

 

[ozil]

Thats help ! Thank you.
So how about now if the last returned column I want to have returned values depends on drop down list so would need to look thru columns to give the values

 

[JamesCanale]

If the last column is data region, somewhere - say cell z1 I would put:

=SORT(UNIQUE(DATA[Region]))

then in the data validation in cell -say- y1, select LIST and set it to

=Z1#

Then the final formula would be:

=SORT(CHOOSECOLS(FILTER(DATA[[Region]:[WTD 3 Average]],(DATA[Region]=Sheet1!N7)*(DATA[Whatever column 2 is called]>800)*(DATA[Region]=Y1)),1,2,3,4,5,19),6,-1)

For filtering - multiplying is AND, and adding is OR. Always put parenthesis around the =<> expressions. And use parenthesis (often confusingly) to construct a complicated set of ANDs and ORs if needed.

 

[ozil]

xlsx: Book1.xlsx

Visit this link to preview the document Book1.xlsx

files.fm

Hi please see file uploaded here. Think could be easier for you to understand and work on to help. Thank you

 

[JamesCanale]

It'll never let me download files here. xl2bb could work.

 

[ozil]

Hi
My work pc wont let me download I think. I will try to work and get it thru xl2bb for you but in meantime see picture to give you idea view

 

[ozil]

Any thoughts on the screenshot ? Its all as table. Unfortunately I cant get xl2bb to work

 

[ozil]

It'll never let me download files here. xl2bb could work.

see below, sortation by Region2 as another drop down would be great too.

Book1.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	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV
1	Region	Region2	HUB	Employee Name	Role	Contract	30-Dec	OT 30-Dec	06-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	03-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	03-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	Column1	31-Mar	OT 31-Mar	07-Apr	OT 07-Apr	14-Apr	OT 14-Apr	21-Apr	OT 21-Apr	28-Apr	OT 28-Apr	Apr Total Hours	Apr Average Hours	Apr Total OT Hours	Apr OT Cost	Column2	WTD 1 Total	WTD 1 Average	Column8	05-May	OT 05-May	12-May	OT 12-May	19-May	OT 19-May	26-May	OT 26-May	May Total Hours	May Average Hours	May Total OT Hours	May OT Cost	Column47	02-Jun	OT 02-Jun	09-Jun	OT 09-Jun	16-Jun	OT 16-Jun	23-Jun	OT 23-Jun	Jun Total Hours	Jun Average Hours	Jun Total OT Hours	Jun OT Cost	Column56	30-Jun	OT 30-Jun	07-Jul	OT 07-Jul	14-Jul	OT 14-Jul	21-Jul	OT 21-Jul	28-Jul	OT 28-Jul	Jul Total Hours	Jul Average Hours	Jul Total OT Hours	Jul OT Cost	Column66	04-Aug	OT 04-Aug	11-Aug	OT 11-Aug	18-Aug	OT 18-Aug	25-Aug	OT 25-Aug	Aug Total Hours	Aug Average Hours	Aug Total OT Hours	Aug OT Cost	Column75	WTD 2 Total	WTD 2 Average	Column78	01-Sep	OT 01-Sep	08-Sep	OT 08-Sep	15-Sep	OT 15-Sep	22-Sep	OT 22-Sep	Sep Total Hours	Sep Average Hours	Sep Total OT Hours	Sep OT Cost	Column3	29-Sep	OT 29-Sep	06-Oct	OT 06-Oct	13-Oct	OT 13-Oct	20-Oct	OT 20-Oct	27-Oct	OT 27-Oct	Oct Total Hours	Oct Avarege Hours	Oct Total OT Hours	Oct OT Cost	Column4	03-Nov	OT 03-Nov	10-Nov	OT 10-Nov	17-Nov	OT 17-Nov	24-Nov	OT 24-Nov	Nov Total Hours	Nov Average Hours	Nov Total OT Hours	Nov OT Cost	Column5	01-Dec	OT 01-Dec	08-Dec	OT 08-Dec	15-Dec	OT 15-Dec	22-Dec	OT 22-Dec	Dec Total Hours	Dec Average Hours	Dec Total OT Hours	Dec OT Cost	Column6	WTD 3 Total	WTD 3 Average
2	South	South Central	BBB	ooooo	WRH	40	40.00	0	42.75	2.75	46.00	6.00	42.00	2.00	46.00	6.00	216.75	43.35	10.75	£174.69		44.00	4.00	42.75	2.75	41.50	1.50	39.75	0	168.00	42.00	14.25	£231.56		40.50	0.50	40.00	0	41.00	1.00		0	121.50	40.50	1.50	£24.38			0		0		0		0		0	0	#DIV/0!	0.00	£0.00		506.25	42.19			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0		0	0	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00		0.00	#DIV/0!			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0		0	0	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00		0.00	#DIV/0!
3	South	South West	AAA	gggggh	WRH	40	42.00	2.00	44.00	4.00	43.75	3.75	41.25	1.25	42.25	2.25	213.25	42.65	11.00	£178.75		42.50	2.50	40.25	0.25	41.50	1.50	45.25	5.25	169.50	42.38	6.50	£105.63		40.00	0	40.25	0.25	40.00	0		0	120.25	40.08	5.50	£89.38			0		0		0		0		0	0	#DIV/0!	0.00	£0.00		503.00	41.92			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0		0	0	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00		0.00	#DIV/0!			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0		0	0	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00		0.00	#DIV/0!
4	South	South East	CCC	fshj	LCV	48	48.00	0	42.00	0	39.50	0	37.50	0	41.50	0	208.50	41.70	0.00	£0.00		42.50	0	42.50	0	42.50	0	42.50	0	170.00	42.50	0.00	£0.00		48.00	0	45.00	0	42.50	0		0	135.50	45.17	0.00	£0.00			0		0		0		0		0	0	#DIV/0!	0.00	£0.00		514.00	42.83			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0		0	0	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00		0.00	#DIV/0!			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0		0	0	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00			0		0		0		0	0.00	#DIV/0!	0.00	£0.00		0.00	#DIV/0!
5
6
7
8
9
10				Drop Down for months ->			as in columns T, AG etc etc
11
12	Region	Region2	HUB	Employee Name	Role	Contract	Cost
13								<- cost for selected month from drop down list depend on columns i.e. Jan OT Cost, Feb OT Cost, etc etc. only for values >over i.e 500
14
15
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
FV2:FV4	Cell Value	between $F2 and 48	text	NO
DQ2:DQ4	Cell Value	between $F2 and 48	text	NO
BL2:BL4	Cell Value	between $F2 and 48	text	NO
FV2:FV4	Expression	=$FV2<$F2	text	NO
FV2:FV4	Expression	=$FV2=$F2	text	NO
FV2:FV4	Expression	=$FV2>48	text	NO
DQ2:DQ4	Expression	=$DQ2<$F2	text	NO
DQ2:DQ4	Expression	=$DQ2=$F2	text	NO
DQ2:DQ4	Expression	=$DQ2>48	text	NO
BL2:BL4	Expression	=$BL2<$F2	text	NO
BL2:BL4	Expression	=$BL2=$F2	text	NO
BL2:BL4	Expression	=$BL2>48	text	NO
T2:T4,AG2:AG4,AT2:AT4,BI2:BI4,BY2:BY4,CL2:CL4,DA2:DA4,DN2:DN4,ED2:ED4,ES2:ES4,FF2:FF4,FS2:FS4	Cell Value	>850	text	NO
T2:T4,AG2:AG4,AT2:AT4,BI2:BI4,BY2:BY4,CL2:CL4,DA2:DA4,DN2:DN4,ED2:ED4,ES2:ES4,FF2:FF4,FS2:FS4	Cell Value	between 650 and 849	text	NO
T2:T4,AG2:AG4,AT2:AT4,BI2:BI4,BY2:BY4,CL2:CL4,DA2:DA4,DN2:DN4,ED2:ED4,ES2:ES4,FF2:FF4,FS2:FS4	Cell Value	between 500 and 649	text	NO

 

[JamesCanale]

I don't quite understand what is needed here, but I had a go:

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	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
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	Column1	31-Mar	OT 31-Mar	7-Apr	OT 07-Apr	14-Apr	OT 14-Apr	21-Apr	OT 21-Apr	28-Apr	OT 28-Apr	Apr Total Hours	Apr Average Hours	Apr Total OT Hours	Apr 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	174.6875		44	4	42.75	2.75	41.5	1.5	39.75	0	168	42	14.25	231.5625		40.5	0.5	40	0	41	1		0	121.5	40.5	1.5	24.375			0		0		0		0		0	0	#DIV/0!	0	0
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	178.75		42.5	2.5	40.25	0.25	41.5	1.5	45.25	5.25	169.5	42.375	6.5	105.625		40	0	40.25	0.25	40	0		0	120.25	40.08333	5.5	89.375			0		0		0		0		0	0	#DIV/0!	0	0
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	0		42.5	0	42.5	0	42.5	0	42.5	0	170	42.5	0	0		48	0	45	0	42.5	0		0	135.5	45.16667	0	0			0		0		0		0		0	0	#DIV/0!	0	0
5
6
7
8
9
10				Drop Down for months ->	Feb		33						Jan	20
11													Feb	33
12	Region	Region2	HUB	Employee Name	Role	Contract	Cost						Mar	46
13	South	South Central	BBB	ooooo	WRH	40	231.5625	<- cost for selected month from drop down list depend on columns i.e. Jan OT Cost, Feb OT Cost, etc etc. only for values >over i.e 500					Apr	61
14	South	South West	AAA	gggggh	WRH	40	105.625						May	77
15	South	South East	CCC	fshj	LCV	48	0						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:G15	A13	=LET(a,A2:F4,b,OFFSET(A2,0,G10-1,ROWS(a),1),HSTACK(a,b))
Dynamic array formulas.

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