Filter Function with multiple criteria from dropdown lists

[zinah]

Hi,

I have managed to get the perfect formula to filter the results by specific number of criteria (dropdown lists). However, I need to add another condition, if any of the criteria is "ALL" then count all population and filter the results accordingly. I'm attaching a sample file to show my formula and the dropdown lists.

The formula I used is

=IFERROR(FILTER(A:D,($B:$B=$H$3)*($C:$C=$H$2)*($D:$D=$H$4)),"No Data")

And what I need is: if I choose any of "All" in criteria 1, then it will count all Departments and filter the results based on other selected criteria, same thing for criteria 1 and 2, if I choose option "ALL", then count all considering the rest of selected criteria.

Job Req ID	Previous company​	Department​	Country​
JR100​	AAA​	HR​	USA​			Criteria 1​	HR​
JR200​	BBB​	Finance​	Australia			Criteria 2​	AAA​
JR300​	CCC​	Operations​	Australia			Criteria 3​	Belgium​
JR400​	DDD​	IT​	Argentina
JR500​	AAA​	HR​	Belgium					Results​
JR600​	FFF​	Finance​	Canada					JR500​	AAA​	HR​	Belgium​
JR700​	GGG​	IT​	Australia					JR900​	AAA​	HR​	Belgium​
JR800​	HHH​	Operations​	Brazil					JR1700​	AAA​	HR​	Belgium​
JR900​	AAA​	HR​	Belgium					JR2100​	AAA​	HR​	Belgium​
JR1000​	BBB​	Customer Service​	Argentina
JR1100​	CCC​	HR​	USA
JR1200​	DDD​	Finance​	USA
JR1300​	EEE​	Operations​	Australia
JR1400​	FFF​	IT​	Brazil
JR1500​	GGG​	HR​	USA
JR1600​	HHH​	Finance​	Argentina
JR1700​	AAA​	HR​	Belgium
JR1800​	BBB​	Operations​	USA
JR1900​	CCC​	Legal​	Australia
JR2000​	DDD​	Customer Service​	USA
JR2100​	AAA​	HR​	Belgium
JR2200​	BBB​	Finance​	Brazil
JR2300​	CCC​	Operations​	Australia
JR2400​	DDD​	IT​	USA
JR2500​	EEE​	HR​	Austria
JR2600​	FFF​	Finance​	USA
JR2700​	GGG​	IT​	USA
JR2800​	HHH​	Operations​	Austria
JR2900​	AAA​	Legal​	Brazil
JR3000​	BBB​	Customer Service​	USA
JR3100​	CCC​	HR​	Austria
JR3200​	DDD​	Finance​	Canada
JR3300​	EEE​	Operations​	Argentina
JR3400​	FFF​	IT​	Austria
JR3500​	GGG​	HR​	Austria
JR3600​	HHH​	Finance​	Canada
JR3700​	AAA​	HR​	Argentina
JR3800​	BBB​	Finance​	Canada
JR3900​	CCC​	Operations​	Austria
JR4000​	DDD​	IT​	Argentina

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Job Req ID	Previous company	Department	Country																Dropdown Lists
2	JR100	AAA	HR	USA			Criteria 1	HR												ALL		ALL		ALL
3	JR200	BBB	Finance	Australia			Criteria 2	AAA												HR		AAA		Argentina
4	JR300	CCC	Operations	Australia			Criteria 3	Belgium												Finance		BBB		Australia
5	JR400	DDD	IT	Argentina																Operations		CCC		Austria
6	JR500	AAA	HR	Belgium					Results											IT		DDD		Belgium
7	JR600	FFF	Finance	Canada					JR500	AAA	HR	Belgium								Legal		EEE		Brazil
8	JR700	GGG	IT	Australia					JR900	AAA	HR	Belgium								Customer Service		FFF		Canada
9	JR800	HHH	Operations	Brazil					JR1700	AAA	HR	Belgium										GGG		USA
10	JR900	AAA	HR	Belgium					JR2100	AAA	HR	Belgium										HHH
11	JR1000	BBB	Customer Service	Argentina
12	JR1100	CCC	HR	USA
13	JR1200	DDD	Finance	USA
14	JR1300	EEE	Operations	Australia
15	JR1400	FFF	IT	Brazil
16	JR1500	GGG	HR	USA
17	JR1600	HHH	Finance	Argentina
18	JR1700	AAA	HR	Belgium
19	JR1800	BBB	Operations	USA
20	JR1900	CCC	Legal	Australia
21	JR2000	DDD	Customer Service	USA
22	JR2100	AAA	HR	Belgium
23	JR2200	BBB	Finance	Brazil
24	JR2300	CCC	Operations	Australia
25	JR2400	DDD	IT	USA
26	JR2500	EEE	HR	Austria
27	JR2600	FFF	Finance	USA
28	JR2700	GGG	IT	USA
29	JR2800	HHH	Operations	Austria
30	JR2900	AAA	Legal	Brazil
31	JR3000	BBB	Customer Service	USA
32	JR3100	CCC	HR	Austria
33	JR3200	DDD	Finance	Canada
34	JR3300	EEE	Operations	Argentina
35	JR3400	FFF	IT	Austria
36	JR3500	GGG	HR	Austria
37	JR3600	HHH	Finance	Canada
38	JR3700	AAA	HR	Argentina
39	JR3800	BBB	Finance	Canada
40	JR3900	CCC	Operations	Austria
41	JR4000	DDD	IT	Argentina
42
43
44
45
46
47
48
Sheet1

Cell Formulas
Range	Formula
I7:L10	I7	=IFERROR(FILTER(A:D,($B:$B=$H$3)*($C:$C=$H$2)*($D:$D=$H$4)),"No Data")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
H2	List	=$T$2:$T$8
H3	List	=$V$2:$V$10
H4	List	=$X$2:$X$9

 

[Fluff]

How about

=FILTER(A:D,IF(H3="ALL",B:B<>"",$B:$B=H3)*IF(H2="ALL",C:C<>"",$C:$C=$H$2)*IF(H4="ALL",D:D<>"",$D:$D=$H$4),"No Data")

 

[zinah]

How about

=FILTER(A:D,IF(H3="ALL",B:B<>"",$B:$B=H3)*IF(H2="ALL",C:C<>"",$C:$C=$H$2)*IF(H4="ALL",D:D<>"",$D:$D=$H$4),"No Data")

Thank you as usual, but what about if I want to include column 1 (job req) and column 3 (department)?

 

[Fluff]

Not sure what you mean. You're not filtering on the job req.

 

[zinah]

Not sure what you mean. You're not filtering on the job req.

What I meant, is instead of displaying 4 columns in the results, I would display 2 only.

 

[Fluff]

Ok how about

=FILTER(FILTER(A:D,IF(H3="ALL",B:B<>"",$B:$B=H3)*IF(H2="ALL",C:C<>"",$C:$C=$H$2)*IF(H4="ALL",D:D<>"",$D:$D=$H$4),"No Data"),ISODD(COLUMN(A:D)))

 

[zinah]

Ok how about

=FILTER(FILTER(A:D,IF(H3="ALL",B:B<>"",$B:$B=H3)*IF(H2="ALL",C:C<>"",$C:$C=$H$2)*IF(H4="ALL",D:D<>"",$D:$D=$H$4),"No Data"),ISODD(COLUMN(A:D)))

That's clever solution, I never knew about

ISODD

or even

ISEVEN

formula. As usual, thank you so much, I really learned a lot from you and the rest of the members here.

 

[Fluff]

Glad to help & thanks for the feedback.