Spans and Layers with condition

[zinah]

Hi,

I'm trying to create spans and layers and I was able to do that, however, I need to create a new span and layers with condition below:
If management chain level 2 = Manager C or Manager G or Manager J, then exclude the count, do you have any suggestions for suitable formula? the current formula is = COUNTIF(C3:C102,"")

Layers under Management Level.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Employee Report Data													Summarization Table
2	Employee ID	Management Chain - Level 02	Management Chain - Level 03	Management Chain - Level 04	Management Chain - Level 05	Management Chain - Level 06	Management Chain - Level 07	Management Chain - Level 08						Management Chain - Level 02	Spans/Layers	Management Chain - Level 03	Management Chain - Level 04	Management Chain - Level 05	Management Chain - Level 06	Management Chain - Level 07	Management Chain - Level 08
3	1	Manager A	Employee	Employee	Employee	Employee	Employee	Employee						Manager A	6	4	4	7	8	9	10
4	2	Manager B	Employee											Manager B	6	10	11	11	12	13	14
5	3	Manager C	Employee	Employee	Employee									Manager C	5	2	3	3	6	6	7
6	4	Manager D	Employee	Employee										Manager D	4	2	2	4	5	6	6
7	5	Manager E	Employee	Employee	Employee									Manager E	4	2	5	5	7	8	8
8	6	Manager F	Employee	Employee	Employee	Employee								Manager F	4	3	4	4	4	8	8
9	7	Manager G	Employee	Employee	Employee									Manager G	3	3	7	7	8	8	8
10	8	Manager H												Manager H	0	8	8	8	8	8	8
11	9	Manager I	Employee	Employee	Employee	Employee								Manager I	4	4	5	8	8	10	10
12	10	Manager J												Manager J	0	8	8	8	8	8	8
13	11	Manager K	Employee	Employee	Employee	Employee	Employee	Employee						Manager K	6	5	6	8	8	8	8
14	12	Manager A	Employee	Employee
15	13	Manager B	Employee	Employee	Employee	Employee
16	14	Manager C	Employee	Employee	Employee
17	15	Manager D	Employee	Employee	Employee
18	16	Manager E	Employee												HC under each layer
19	17	Manager F	Employee	Employee	Employee	Employee								CEO - 1	1
20	18	Manager G	Employee											CEO - 2	8
21	19	Manager H												CEO - 3	51
22	20	Manager I	Employee	Employee	Employee	Employee								CEO - 4	63
23	21	Manager J												CEO - 5	73
24	22	Manager K	Employee	Employee										CEO - 6	82
25	23	Manager A	Employee	Employee										CEO - 7	92
26	24	Manager B	Employee	Employee	Employee	Employee	Employee							CEO - 8	95
27	25	Manager C	Employee	Employee	Employee	Employee	Employee
28	26	Manager D	Employee	Employee	Employee	Employee
29	27	Manager E	Employee	Employee	Employee
30	28	Manager F	Employee	Employee	Employee	Employee
31	29	Manager G	Employee
32	30	Manager H
33	31	Manager I	Employee	Employee
34	32	Manager J
35	33	Manager K	Employee	Employee
36	34	Manager A	Employee	Employee
37	35	Manager B	Employee	Employee	Employee
38	36	Manager C	Employee	Employee	Employee
39	37	Manager D	Employee	Employee
40	38	Manager E	Employee
41	39	Manager F	Employee	Employee	Employee	Employee
42	40	Manager G	Employee
43	41	Manager H
44	42	Manager I	Employee	Employee
45	43	Manager J
46	44	Manager K	Employee	Employee	Employee	Employee	Employee	Employee
47	45	Manager E	Employee
48	46	Manager F
49	47	Manager G	Employee
50	48	Manager H
51	49	Manager I	Employee	Employee
52	50	Manager J
53	51	Manager K	Employee	Employee	Employee	Employee	Employee	Employee
54	52	Manager A	Employee	Employee	Employee	Employee	Employee
55	53	Manager A
56	54	Manager A	Employee	Employee	Employee	Employee
57	55	Manager A
58	56	Manager A	Employee	Employee	Employee
59	57	Manager A
60	58	Manager A
61	59	Manager B
62	60	Manager B
63	61	Manager B
64	62	Manager B	Employee	Employee	Employee	Employee	Employee	Employee
65	63	Manager B
66	64	Manager B
67	65	Manager B
68	66	Manager B
69	67	Manager B
70	68	Manager B
71	69	Manager B
72	70	Manager C	Employee
73	71	Manager C
74	72	Manager C
75	73	Manager D
76	74	Manager D
77	75	Manager E
78	76	Manager F
79	77	Manager G
80	78	Manager H
81	79	Manager I
82	80	Manager J
83	81	Manager K
84	82	Manager E	Employee	Employee	Employee	Employee
85	83	Manager F	Employee
86	84	Manager G
87	85	Manager H
88	86	Manager I	Employee
89	87	Manager J
90	88	Manager K
91	89	Manager E
92	90	Manager F
93	91	Manager G
94	92	Manager H
95	93	Manager I
96	94	Manager J
97	95	Manager K
98	96	Manager K
99	97	Manager K
100	98	Manager K	Employee
101	99	Manager I
102	100	Manager I
Sheet1

Cell Formulas
Range	Formula
O3:O13	O3	=MAX(MMULT(($B$3:$B$1000=N3)*($C$3:$H$1000<>""),SEQUENCE(6,,,0)))
P3:U13	P3	=COUNTIFS($B$3:$B$102,$N3,C$3:C$102,"")
O20	O20	=COUNTA(N3:N13)-3
O21	O21	=COUNTIF(C3:C102,"")
O22	O22	=COUNTIF(D3:D102,"")
O23	O23	=COUNTIF(E3:E102,"")
O24	O24	=COUNTIF(F3:F102,"")
O25	O25	=COUNTIF(G3:G102,"")
O26	O26	=COUNTIF(H3:H102,"")

 

[Anonymous1378]

Do you mean something like =COUNTIFS(C3:C102,"",B3:B102,"<>Manager C",B3:B102,"<>Manager G",B3:B102,"<>Manager J")?

 

[zinah]

Do you mean something like =COUNTIFS(C3:C102,"",B3:B102,"<>Manager C",B3:B102,"<>Manager G",B3:B102,"<>Manager J")?

Thanks a lot for your great formula! what about if I need to use unique formula to populate managers list based on specific manager that they report to? i.e. in below table, I need to list the managers that reports to manager 1 only and exclude managers that report to manager 2? The end result should be the last column

Layers under Management Level.xlsx
	M	N	O	P
31	Management Chain - Level 01	Management Chain - Level 02	Unique Formula	Managers under Manager 1
32	Manager 1	Manager A	Manager A	Manager A
33	Manager 1	Manager B	Manager B	Manager B
34	Manager 2	Manager C	Manager C	Manager D
35	Manager 1	Manager D	Manager D	Manager E
36	Manager 1	Manager E	Manager E	Manager F
37	Manager 1	Manager F	Manager F	Manager H
38	Manager 2	Manager G	Manager G	Manager I
39	Manager 1	Manager H	Manager H	Manager K
40	Manager 1	Manager I	Manager I
41	Manager 2	Manager J	Manager J
42	Manager 1	Manager K	Manager K
Sheet1

Cell Formulas
Range	Formula
O32:O42	O32	=UNIQUE(B3:B102)
Dynamic array formulas.

 

[Anonymous1378]

Something like this?

Book24
	M	N	O	P
31	Management Chain - Level 01	Management Chain - Level 02	Unique Formula	Managers under Manager 1
32	Manager 1	Manager A	Manager A	Manager A
33	Manager 1	Manager B	Manager B	Manager B
34	Manager 2	Manager C	Manager C	Manager D
35	Manager 1	Manager D	Manager D	Manager E
36	Manager 1	Manager E	Manager E	Manager F
37	Manager 1	Manager F	Manager F	Manager H
38	Manager 2	Manager G	Manager G	Manager I
39	Manager 1	Manager H	Manager H	Manager K
40	Manager 1	Manager I	Manager I
41	Manager 2	Manager J	Manager J
42	Manager 1	Manager K	Manager K
Sheet1

Cell Formulas
Range	Formula
P32:P39	P32	=FILTER(N32:N42,M32:M42="Manager 1")
Dynamic array formulas.

 

[zinah]

FILTER(N32:N42,M32:M42="Manager 1")

amazing! the formula is what I really needed. Thanks a million!