Count the blanks based on criteria

[zinah]

Hi,

I have below table which has management chain levels from 2 to 8, and managers 11 managers. What I need to know is how to calculate the blank cells under Management Chain Level 03 for each manager. Meaning, if I filter Manager A, and filter Management Chain Level 03 by blank only, the result should be 4. I did pivot table to get the results of blanks for each manager, but the challenge is I need to count the blanks of each column (C-H) for each manager. Is there any way that could help rather than painfully do pivot for each level separately?

Layers under Management Level.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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
3	1	Manager A	Employee	Employee	Employee	Employee	Employee	Employee						Manager A	6
4	2	Manager B	Employee											Manager B	6
5	3	Manager C	Employee	Employee	Employee									Manager C	5
6	4	Manager D	Employee	Employee										Manager D	4
7	5	Manager E	Employee	Employee	Employee									Manager E	4
8	6	Manager F	Employee	Employee	Employee	Employee								Manager F	4
9	7	Manager G	Employee	Employee	Employee									Manager G	3
10	8	Manager H												Manager H	0
11	9	Manager I	Employee	Employee	Employee	Employee								Manager I	4
12	10	Manager J												Manager J	0
13	11	Manager K	Employee	Employee	Employee	Employee	Employee	Employee						Manager K	6
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											Row Labels	Count of Employee ID
19	17	Manager F	Employee	Employee	Employee	Employee								Manager A	11
20	18	Manager G	Employee											Employee	7
21	19	Manager H												(blank)	4
22	20	Manager I	Employee	Employee	Employee	Employee								Manager B	15
23	21	Manager J												Employee	5
24	22	Manager K	Employee	Employee										(blank)	10
25	23	Manager A	Employee	Employee										Manager C	7
26	24	Manager B	Employee	Employee	Employee	Employee	Employee							Employee	5
27	25	Manager C	Employee	Employee	Employee	Employee	Employee							(blank)	2
28	26	Manager D	Employee	Employee	Employee	Employee								Manager D	6
29	27	Manager E	Employee	Employee	Employee									Employee	4
30	28	Manager F	Employee	Employee	Employee	Employee								(blank)	2
31	29	Manager G	Employee											Manager E	8
32	30	Manager H												Employee	6
33	31	Manager I	Employee	Employee										(blank)	2
34	32	Manager J												Manager F	8
35	33	Manager K	Employee	Employee										Employee	5
36	34	Manager A	Employee	Employee										(blank)	3
37	35	Manager B	Employee	Employee	Employee									Manager G	8
38	36	Manager C	Employee	Employee	Employee									Employee	5
39	37	Manager D	Employee	Employee										(blank)	3
40	38	Manager E	Employee											Manager H	8
41	39	Manager F	Employee	Employee	Employee	Employee								(blank)	8
42	40	Manager G	Employee											Manager I	10
43	41	Manager H												Employee	6
44	42	Manager I	Employee	Employee										(blank)	4
45	43	Manager J												Manager J	8
46	44	Manager K	Employee	Employee	Employee	Employee	Employee	Employee						(blank)	8
47	45	Manager E	Employee											Manager K	11
48	46	Manager F												Employee	6
49	47	Manager G	Employee											(blank)	5
50	48	Manager H												Grand Total	100
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)))

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
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	Level 3	Level 4	Level 5	Level 6	Level 7	Level 8
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
19	17	Manager F	Employee	Employee	Employee	Employee
20	18	Manager G	Employee
21	19	Manager H
22	20	Manager I	Employee	Employee	Employee	Employee
23	21	Manager J
24	22	Manager K	Employee	Employee
25	23	Manager A	Employee	Employee
26	24	Manager B	Employee	Employee	Employee	Employee	Employee
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
Data

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,"")

 

[zinah]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
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	Level 3	Level 4	Level 5	Level 6	Level 7	Level 8
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
19	17	Manager F	Employee	Employee	Employee	Employee
20	18	Manager G	Employee
21	19	Manager H
22	20	Manager I	Employee	Employee	Employee	Employee
23	21	Manager J
24	22	Manager K	Employee	Employee
25	23	Manager A	Employee	Employee
26	24	Manager B	Employee	Employee	Employee	Employee	Employee
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
Data

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,"")

I cannot thank you enough for your usual prompt support , it worked perfectly great and I even added more criteria

 

[Fluff]

You're welcome & thanks for the feedback.