Span of Control Count

[zinah]

Hi,

I have a report that have all management levels (management layers) and what I need is to count how many layers under each manager from Management Chain 02. I have the below sample table and want a formula that can help with accomplishing my target. The report I have has more 8000 rows and below is just a sample. And what I need is a formula that can help with the summarization table:

Layers under Management Level.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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
4	2	Manager B	Employee												Manager B
5	3	Manager C	Employee	Employee	Employee										Manager C
6	4	Manager D	Employee	Employee											Manager D
7	5	Manager E	Employee												Manager E
8	6	Manager F	Employee	Employee	Employee	Employee									Manager F
9	7	Manager G	Employee												Manager G
10	8	Manager H	Employee	Employee	Employee	Employee	Employee	Employee							Manager H
11	9	Manager I	Employee	Employee	Employee	Employee									Manager I
12	10	Manager J	Employee	Employee	Employee	Employee	Employee								Manager J
13	11	Manager K	Employee	Employee	Employee	Employee	Employee	Employee							Manager K
14	12	Manager A	Employee	Employee
15	13	Manager B	Employee
16	14	Manager C	Employee	Employee	Employee
17	15	Manager D	Employee	Employee
18	16	Manager E	Employee
19	17	Manager F	Employee	Employee	Employee	Employee
20	18	Manager G	Employee
21	19	Manager H	Employee	Employee	Employee	Employee	Employee	Employee
22	20	Manager I	Employee	Employee	Employee	Employee
23	21	Manager J	Employee	Employee
24	22	Manager K	Employee	Employee
25	23	Manager A	Employee	Employee
26	24	Manager B	Employee
27	25	Manager C	Employee	Employee	Employee
28	26	Manager D	Employee	Employee
29	27	Manager E	Employee
30	28	Manager F	Employee	Employee	Employee	Employee
31	29	Manager G	Employee
32	30	Manager H	Employee	Employee	Employee	Employee	Employee	Employee
33	31	Manager I	Employee	Employee
34	32	Manager J	Employee	Employee
35	33	Manager K	Employee	Employee
36	34	Manager A	Employee	Employee
37	35	Manager B	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	Employee	Employee
44	42	Manager I	Employee	Employee
45	43	Manager J	Employee	Employee
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	Employee	Employee
51	49	Manager I	Employee	Employee
52	50	Manager J	Employee	Employee
53	51	Manager K	Employee	Employee	Employee	Employee	Employee	Employee
Sheet1

 

[Fluff]

How about

=SUMPRODUCT(($B$3:$B$1000=O3)*($C$3:$H$1000<>""))

 

[zinah]

How about

=SUMPRODUCT(($B$3:$B$1000=O3)*($C$3:$H$1000<>""))

Thank you for your help, but what I actually need is to count how many management chain under each manager, i.e. Manager A may have 4 spans, Manager B may have 2 and so on, the total span should not exceed 6 since I have 7 management chain and want to count how many chain under management chain 02, does that make sense?
Below is the result I got from your suggested formula:

Summarization Table
Management Chain - Level 02	Spans/Layers
Manager A	12
Manager B	4
Manager C	12
Manager D	8
Manager E	5
Manager F	16
Manager G	5
Manager H	22
Manager I	14
Manager J	13
Manager K	22

 

[Fluff]

does that make sense?

Nope, fraid not. What exactly do you mean by a "management chain"

 

[zinah]

Nope, fraid not. What exactly do you mean by a "management chain"

The management chain is the column header, which stand for management layer, each employee has management chains/hierarchy that he/she reports to. What I need to get is all managers under management chain 02 (which is the summary table I have included) and how many chains/layers they have under them. Since I have Management Chain from 02 to 08 and I need to count the layers from 03 to 08, each manager under management chain 02 should not exceed the count of 6. Hope I answered your question.

 

[Fluff]

That still does not tell me what you are trying to do.
Manager A appears 4 times in col B, which row should the formula look at to determine the count?

 

[zinah]

That still does not tell me what you are trying to do.
Manager A appears 4 times in col B, which row should the formula look at to determine the count?

This is expected as each employee has designated management chain(s), so the managers may appear more than one time since I have listed in this sample list 51 employees. Actually explaining this to you, inspired me to get the right formula which is as below table, thank you so much for the inspiration

Layers under Management Level.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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	Spans/Layers						Management Chain - Level 02	Spans/Layers
3	1	Manager A	Employee	Employee	Employee	Employee	Employee	Employee	6						Manager A	6
4	2	Manager B	Employee						1						Manager B	1
5	3	Manager C	Employee	Employee	Employee				3						Manager C	3
6	4	Manager D	Employee	Employee					2						Manager D	2
7	5	Manager E	Employee						1						Manager E	1
8	6	Manager F	Employee	Employee	Employee	Employee			4						Manager F	4
9	7	Manager G	Employee						1						Manager G	1
10	8	Manager H	Employee	Employee	Employee	Employee	Employee	Employee	6						Manager H	6
11	9	Manager I	Employee	Employee	Employee	Employee			4						Manager I	4
12	10	Manager J	Employee	Employee	Employee	Employee	Employee		5						Manager J	5
13	11	Manager K	Employee	Employee	Employee	Employee	Employee	Employee	6						Manager K	6
14	12	Manager A	Employee	Employee					2
15	13	Manager B	Employee						1
16	14	Manager C	Employee	Employee	Employee				3
17	15	Manager D	Employee	Employee					2
18	16	Manager E	Employee						1
19	17	Manager F	Employee	Employee	Employee	Employee			4
20	18	Manager G	Employee						1
21	19	Manager H	Employee	Employee	Employee	Employee	Employee	Employee	6
22	20	Manager I	Employee	Employee	Employee	Employee			4
23	21	Manager J	Employee	Employee					2
24	22	Manager K	Employee	Employee					2
25	23	Manager A	Employee	Employee					2
26	24	Manager B	Employee						1
27	25	Manager C	Employee	Employee	Employee				3
28	26	Manager D	Employee	Employee					2
29	27	Manager E	Employee						1
30	28	Manager F	Employee	Employee	Employee	Employee			4
31	29	Manager G	Employee						1
32	30	Manager H	Employee	Employee	Employee	Employee	Employee	Employee	6
33	31	Manager I	Employee	Employee					2
34	32	Manager J	Employee	Employee					2
35	33	Manager K	Employee	Employee					2
36	34	Manager A	Employee	Employee					2
37	35	Manager B	Employee						1
38	36	Manager C	Employee	Employee	Employee				3
39	37	Manager D	Employee	Employee					2
40	38	Manager E	Employee						1
41	39	Manager F	Employee	Employee	Employee	Employee			4
42	40	Manager G	Employee						1
43	41	Manager H	Employee	Employee					2
44	42	Manager I	Employee	Employee					2
45	43	Manager J	Employee	Employee					2
46	44	Manager K	Employee	Employee	Employee	Employee	Employee	Employee	6
47	45	Manager E	Employee						1
48	46	Manager F							0
49	47	Manager G	Employee						1
50	48	Manager H	Employee	Employee					2
51	49	Manager I	Employee	Employee					2
52	50	Manager J	Employee	Employee					2
53	51	Manager K	Employee	Employee	Employee	Employee	Employee	Employee	6
Sheet1

Cell Formulas
Range	Formula
P3:P13	P3	=MAXIFS($I$3:$I$53,$B$3:$B$53,O3)
I3:I53	I3	=COUNTA(C3:H3)

 

[Fluff]

Thanks for that.
You can do that without a helper column, like

=MAX(MMULT(($B$3:$B$1000=O3)*($C$3:$H$1000<>""),SEQUENCE(6,,,0)))

 

[zinah]

Thanks for that.
You can do that without a helper column, like

=MAX(MMULT(($B$3:$B$1000=O3)*($C$3:$H$1000<>""),SEQUENCE(6,,,0)))

Wow, that's even much better, I knew you would have a great formula (as usual), thank you so much for your usual great support, I really learned a lot from you since I joined this forum.

 

[Fluff]

Glad to help & thanks for the feedback.