Filter Function with Multiple Criteria

zinah · Feb 2, 2023

Hello,

I have below sample table and what I need to get is the list of employee names that report to the manager table only, I tried to use filter formula(below) but it didn't work. Not sure why I was not able to paste the data using XL2BB. Can anyone suggest a better formula?

Thanks!

Column A Column B Column C Column F

Employee ID	Employee Name	Employee's Manager	Managers Table
1	A	JJ	JJ
2	B	KK	KK
3	C	LL	LL
4	D	MM	MM
5	E	NN	NN
6	F	OO	OO
7	G	PP	PP	Formula
8	H	JJ		=FILTER($B$2:$B$41,$C$2:$C$41=F2:F8)
9	I	KK
10	J	LL
11	K	MM
12	L	NN
13	M	OO
14	N	PP
15	O	JJ
16	P	KK
17	Q	LL
18	R	MM
19	S	NN
20	T	OO
21	U	PP
22	V	JJ
23	X	KK
24	Y	LL
25	Z	MM
26	AA	NN
27	BB	OO
28	CC	PP
29	DD	JJ
30	EE	KK
31	FF	LL
32	KKK	Y21
33	LLL	Y21
34	PPP	Y21
35	UUU	Y21
36	RRR	Y21
37	TTT	K21
38	SSS	K21
39	S21	K21
40	S45	K21

JvdV · Feb 2, 2023

Book1

A

B

C

D

E

F

G

H

1

Employee ID

Employee Name

Employee's Manager

Managers Table

Formula

2

1

A

JJ

A

3

2

B

KK

B

4

3

C

LL

C

5

4

D

MM

D

6

5

E

NN

E

7

6

F

OO

F

8

7

G

PP

G

9

8

H

JJ

H

10

9

I

KK

I

11

10

J

LL

J

12

11

K

MM

K

13

12

L

NN

L

14

13

M

OO

M

15

14

N

PP

N

16

15

O

JJ

O

17

16

P

KK

P

18

17

Q

LL

Q

19

18

R

MM

R

20

19

S

NN

S

21

20

T

OO

T

22

21

U

PP

U

23

22

V

JJ

V

24

23

X

KK

X

25

24

Y

LL

Y

26

25

Z

MM

Z

27

26

AA

NN

AA

28

27

BB

OO

BB

29

28

CC

PP

CC

30

29

DD

JJ

DD

31

30

EE

KK

EE

32

31

FF

LL

FF

33

32

KKK

Y21

34

33

LLL

Y21

35

34

PPP

Y21

36

35

UUU

Y21

37

36

RRR

Y21

38

37

TTT

K21

39

38

SSS

K21

40

39

S21

K21

41

40

S45

K21

Sheet10

Cell Formulas
Range	Formula
H2:H32	H2	=FILTER(B2:B41,COUNTIF(F2:F8,C2:C41))
Dynamic array formulas.

zinah · Feb 2, 2023

JvdV said:
Book1
A B C D E F G H
1 Employee ID Employee Name Employee's Manager Managers Table Formula
2 1 A JJ JJ A
3 2 B KK KK B
4 3 C LL LL C
5 4 D MM MM D
6 5 E NN NN E
7 6 F OO OO F
8 7 G PP PP G
9 8 H JJ H
10 9 I KK I
11 10 J LL J
12 11 K MM K
13 12 L NN L
14 13 M OO M
15 14 N PP N
16 15 O JJ O
17 16 P KK P
18 17 Q LL Q
19 18 R MM R
20 19 S NN S
21 20 T OO T
22 21 U PP U
23 22 V JJ V
24 23 X KK X
25 24 Y LL Y
26 25 Z MM Z
27 26 AA NN AA
28 27 BB OO BB
29 28 CC PP CC
30 29 DD JJ DD
31 30 EE KK EE
32 31 FF LL FF
33 32 KKK Y21
34 33 LLL Y21
35 34 PPP Y21
36 35 UUU Y21
37 36 RRR Y21
38 37 TTT K21
39 38 SSS K21
40 39 S21 K21
41 40 S45 K21
Sheet10
Cell Formulas
Range Formula
H2:H32 H2 =FILTER(B2:B41,COUNTIF(F2:F8,C2:C41))
Dynamic array formulas.

Thank you so much! what about if I add two more criteria, which is count the R from column D and Y from column E?
Column A Column B Column C Column D Column E Column G

Employee ID	Employee Name	Employee's Manager	Type	Status	Managers Table
1	A	JJ	R	Y	JJ
2	B	KK	R	Y	KK
3	C	LL	R	Y	LL
4	D	MM	R	N	MM
5	E	NN	R	N	NN
6	F	OO	R	N	OO
7	G	PP	R	N	PP	=FILTER(B2:B41,COUNTIFS(G2:G8,C2:C41,D2:D41,"R",E2:E41,"Y"))
8	H	JJ	R	N		#VALUE!
9	I	KK	R	N
10	J	LL	R	N
11	K	MM	R	N
12	L	NN	R	N
13	M	OO	R	Y
14	N	PP	R	Y
15	O	JJ	R	Y
16	P	KK	R	N
17	Q	LL	R	N
18	R	MM	R	N
19	S	NN	R	N
20	T	OO	R	N
21	U	PP	R	N
22	V	JJ	R	N
23	X	KK	R	N
24	Y	LL	R	N
25	Z	MM	R	Y
26	AA	NN	R	Y
27	BB	OO	R	Y
28	CC	PP	R	N
29	DD	JJ	R	N
30	EE	KK	R	N
31	FF	LL	C	N
32	KKK	Y21	R	N
33	LLL	Y21	R	N
34	PPP	Y21	R	N
35	UUU	Y21	R	N
36	RRR	Y21	R	N
37	TTT	K21	C	Y
38	SSS	K21	R	Y
39	S21	K21	R	Y
40	S45	K21	R	Y

zinah · Feb 7, 2023

Hello everyone!

Can anyone help with enhancing the suggested formula to add more criteria to the filter function? The formula I tried using to add 2 more criteria didn't work, not sure what I'm missing:
Here's the formula:
add two more criteria, which is count the R from column D and Y from column E
=FILTER(B2:B41,COUNTIFS(G2:G8,C2:C41,D2:D41,"R",E2:E41,"Y"))

Sufiyan97 · Feb 7, 2023

Try

Book5

A

B

C

D

E

F

G

H

I

J

K

L

1

Employee ID

Employee Name

Employee's Manager

Type

Status

Managers Table

Type

Status

2

1

A

JJ

R

Y

JJ

R

Y

A

3

2

B

KK

R

Y

KK

B

4

3

C

LL

R

Y

LL

C

5

4

D

MM

R

N

MM

M

6

5

E

NN

R

N

NN

N

7

6

F

OO

R

N

OO

O

8

7

G

PP

R

N

PP

Z

9

8

H

JJ

R

N

AA

10

9

I

KK

R

N

BB

11

10

J

LL

R

N

12

11

K

MM

R

N

13

12

L

NN

R

N

14

13

M

OO

R

Y

15

14

N

PP

R

Y

16

15

O

JJ

R

Y

17

16

P

KK

R

N

18

17

Q

LL

R

N

19

18

R

MM

R

N

20

19

S

NN

R

N

21

20

T

OO

R

N

22

21

U

PP

R

N

23

22

V

JJ

R

N

24

23

X

KK

R

N

25

24

Y

LL

R

N

26

25

Z

MM

R

Y

27

26

AA

NN

R

Y

28

27

BB

OO

R

Y

29

28

CC

PP

R

N

30

29

DD

JJ

R

N

31

30

EE

KK

R

N

32

31

FF

LL

C

N

33

32

KKK

Y21

R

N

34

33

LLL

Y21

R

N

35

34

PPP

Y21

R

N

36

35

UUU

Y21

R

N

37

36

RRR

Y21

R

N

38

37

TTT

K21

C

Y

39

38

SSS

K21

R

Y

40

39

S21

K21

R

Y

41

40

S45

K21

R

Y

42

Sheet3

Cell Formulas
Range	Formula
J2:J10	J2	=FILTER(B2:B41,ISNUMBER(MATCH(C2:C41,G2:G8,0))ISNUMBER(MATCH(D2:D41,H2,0))ISNUMBER(MATCH(E2:E41,I2,0)))
Dynamic array formulas.

JvdV · Feb 8, 2023

Book1

A

B

C

D

E

F

G

H

I

1

mployee ID

Employee Name

Employee's Manager

Type

Status

Managers Table

2

1

A

JJ

R

Y

JJ

A

3

2

B

KK

R

Y

KK

B

4

3

C

LL

R

Y

LL

C

5

4

D

MM

R

N

MM

M

6

5

E

NN

R

N

NN

N

7

6

F

OO

R

N

OO

O

8

7

G

PP

R

N

PP

Z

9

8

H

JJ

R

N

AA

10

9

I

KK

R

N

BB

11

10

J

LL

R

N

12

11

K

MM

R

N

13

12

L

NN

R

N

14

13

M

OO

R

Y

15

14

N

PP

R

Y

16

15

O

JJ

R

Y

17

16

P

KK

R

N

18

17

Q

LL

R

N

19

18

R

MM

R

N

20

19

S

NN

R

N

21

20

T

OO

R

N

22

21

U

PP

R

N

23

22

V

JJ

R

N

24

23

X

KK

R

N

25

24

Y

LL

R

N

26

25

Z

MM

R

Y

27

26

AA

NN

R

Y

28

27

BB

OO

R

Y

29

28

CC

PP

R

N

30

29

DD

JJ

R

N

31

30

EE

KK

R

N

32

31

FF

LL

C

N

33

32

KKK

Y21

R

N

34

33

LLL

Y21

R

N

35

34

PPP

Y21

R

N

36

35

UUU

Y21

R

N

37

36

RRR

Y21

R

N

38

37

TTT

K21

C

Y

39

38

SSS

K21

R

Y

40

39

S21

K21

R

Y

41

40

S45

K21

R

Y

Sheet5

Cell Formulas
Range	Formula
I2:I10	I2	=FILTER(B2:B41,(COUNTIF(G2:G8,C2:C41))(D2:D41="R")(E2:E41="Y"))
Dynamic array formulas.

zinah · Feb 8, 2023

Sufiyan97 said:
Try

Book5
A B C D E F G H I J K L
1 Employee ID Employee Name Employee's Manager Type Status Managers Table Type Status
2 1 A JJ R Y JJ R Y A
3 2 B KK R Y KK B
4 3 C LL R Y LL C
5 4 D MM R N MM M
6 5 E NN R N NN N
7 6 F OO R N OO O
8 7 G PP R N PP Z
9 8 H JJ R N AA
10 9 I KK R N BB
11 10 J LL R N
12 11 K MM R N
13 12 L NN R N
14 13 M OO R Y
15 14 N PP R Y
16 15 O JJ R Y
17 16 P KK R N
18 17 Q LL R N
19 18 R MM R N
20 19 S NN R N
21 20 T OO R N
22 21 U PP R N
23 22 V JJ R N
24 23 X KK R N
25 24 Y LL R N
26 25 Z MM R Y
27 26 AA NN R Y
28 27 BB OO R Y
29 28 CC PP R N
30 29 DD JJ R N
31 30 EE KK R N
32 31 FF LL C N
33 32 KKK Y21 R N
34 33 LLL Y21 R N
35 34 PPP Y21 R N
36 35 UUU Y21 R N
37 36 RRR Y21 R N
38 37 TTT K21 C Y
39 38 SSS K21 R Y
40 39 S21 K21 R Y
41 40 S45 K21 R Y
42
Sheet3
Cell Formulas
Range Formula
J2:J10 J2 =FILTER(B2:B41,ISNUMBER(MATCH(C2:C41,G2:G8,0))*ISNUMBER(MATCH(D2:D41,H2,0))*ISNUMBER(MATCH(E2:E41,I2,0)))
Dynamic array formulas.

Thank you so much for your help

, your suggested formula worked perfectly fine!

zinah · Feb 8, 2023

JvdV said:
Book1
A B C D E F G H I
1 mployee ID Employee Name Employee's Manager Type Status Managers Table
2 1 A JJ R Y JJ A
3 2 B KK R Y KK B
4 3 C LL R Y LL C
5 4 D MM R N MM M
6 5 E NN R N NN N
7 6 F OO R N OO O
8 7 G PP R N PP Z
9 8 H JJ R N AA
10 9 I KK R N BB
11 10 J LL R N
12 11 K MM R N
13 12 L NN R N
14 13 M OO R Y
15 14 N PP R Y
16 15 O JJ R Y
17 16 P KK R N
18 17 Q LL R N
19 18 R MM R N
20 19 S NN R N
21 20 T OO R N
22 21 U PP R N
23 22 V JJ R N
24 23 X KK R N
25 24 Y LL R N
26 25 Z MM R Y
27 26 AA NN R Y
28 27 BB OO R Y
29 28 CC PP R N
30 29 DD JJ R N
31 30 EE KK R N
32 31 FF LL C N
33 32 KKK Y21 R N
34 33 LLL Y21 R N
35 34 PPP Y21 R N
36 35 UUU Y21 R N
37 36 RRR Y21 R N
38 37 TTT K21 C Y
39 38 SSS K21 R Y
40 39 S21 K21 R Y
41 40 S45 K21 R Y
Sheet5
Cell Formulas
Range Formula
I2:I10 I2 =FILTER(B2:B41,(COUNTIF(G2:G8,C2:C41))*(D2:D41="R")*(E2:E41="Y"))
Dynamic array formulas.

Thank you so much for your help, your formula helped me understand what was missing in my formula and it worked perfectly great

JvdV · Feb 9, 2023

That's nice to hear! Glad it helped @zinah

Filter Function with Multiple Criteria

zinah

Active Member

Excel Facts

JvdV

Board Regular

zinah

Active Member

zinah

Active Member

Sufiyan97

Well-known Member

JvdV

Board Regular

zinah

Active Member

zinah

Active Member

JvdV

Board Regular

Similar threads

Forum statistics

Share this page

Filter Function with Multiple Criteria

Active Member

Excel Facts

Board Regular

Active Member

Active Member

Well-known Member

Board Regular

Active Member

Active Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock