Formulas to calculate personnel statistics

[timjo]

Hi Forum members,

I have spreadsheet listing department members, the employment status and their team assignment. In another sheet, I have a table to summarise that data. When I updated the personnel list, several of the formulas broken and were not calculating correctly.

Test_Summary sheet

• "Total Personnel" - the total is correct, but the formulas no longer calculate the male and female component
• "Personnel with Multiple roles" - all stats are wrong.
  • Total = total number of people whose name is listed more than once
  • Male = the number of males whose name is listed more than once
  • Female = the number of females whose name is listed more than once

Can I have some assistance to create formula to the create the statistics based on the end outcomes listed above please?

Below is my table with the yellow cells indicating the problem formulas.

Sansitised - Personnel Stats.xlsx
	A	B	C	D	E	F	G	H
1	Section	Total	Male	Female	Admin	Full-time	External Commuter	Internal Commuter
2	Total Personnel	80	0	0
3	Personnel with Multiple Roles	12	22	70
4	HCD	18	6	12
5	FC	44	5	39		16	23	5
6	Medical Services	11	3	8	6	6	3	2
7	Patients - AC	5	1	4
8	Patients - AL	10	3	7
Test_Summary

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))
C2:D2	C2	=IFERROR(ROWS(_xlfn.UNIQUE(_xlfn._xlws.FILTER(Test[Name],Test[Gender]=C1))), 0)
B3	B3	=(COUNTA(Test[Name])-(SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))))
C3:D3	C3	=(COUNTIFS(Test[Name],"*",Test[Gender],C1)-(IFERROR(ROWS(_xlfn.UNIQUE(_xlfn._xlws.FILTER(Test[Name],Test[Gender]=C1))),0)))
B4	B4	=COUNTIF(Test[Section], "HCD")
C4:D4	C4	=COUNTIFS(Test[Section], "HCD", Test[Gender], C1)
B5	B5	=COUNTIF(Test[Section], "FC")
C5:D5	C5	=COUNTIFS(Test[Section], "FC", Test[Gender], C1)
F5	F5	=COUNTIFS(Test[Section], "FC", Test[Status], "P")
G5	G5	=COUNTIFS(Test[Section], "FC", Test[Status], "EC")
H5	H5	=COUNTIFS(Test[Section], "FC", Test[Status], "IC")
B6	B6	=COUNTIF(Test[Section], "Medical*")
C6:D6	C6	=COUNTIFS(Test[Section], "Medical*", Test[Gender], C1)
E6	E6	=COUNTIFS(Test[Section], "Medical*", Test[Section], "*Admin")
F6	F6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "P")
G6	G6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "EC")
H6	H6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "IC")
B7	B7	=COUNTIF(Test[Section], "AC")
C7:D7	C7	=COUNTIFS(Test[Section], "AC", Test[Gender], C1)
B8	B8	=COUNTIF(Test[Section], "AL")
C8:D8	C8	=COUNTIFS(Test[Section], "AL", Test[Gender], C1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Below is the personnel list where the statistics are generated from.

Sansitised - Personnel Stats.xlsx
	A	B	C	D
1	Name	Gender	Status	Section
2	Name 01	Female	IC	FC
3	Name 02	Male	P	Management
4	Name 02	Male	IC	Medical - Physiotherapy
5	Name 03	Female	EC	FC
6	Name 04	Female	EC	FC
7	Name 05	Female	EC	FC
8	Name 06	Female	EC	FC
9	Name 06	Female	EC	HCD
10	Name 07	Female	EC	FC
11	Name 08	Male	EC	FC
12	Name 09	Female	Patient	AL
13	Name 10	Female	Patient	AL
14	Name 11	Male	Patient	AL
15	Name 12	Female	P	FC
16	Name 13	Female	P	FC
17	Name 14	Female	Patient	AL
18	Name 15	Female	P	FC
19	Name 16	Female	IC	Medical - Podiatry
20	Name 17	Female	Patient	AC
21	Name 18	Female	P	Medical - Admin
22	Name 18	Female	P	HCD
23	Name 19	Male	EC	FC
24	Name 20	Female	EC	FC
25	Name 21	Female	EC	FC
26	Name 22	Female	EC	HCD
27	Name 23	Female	P	FC
28	Name 24	Female	EC	HCD
29	Name 25	Female	P	FC
36	Name 30	Female	P	HCD
37	Name 31	Female	EC	FC
38	Name 32	Female	Patient	AC
39	Name 33	Male	EC	FC
40	Name 34	Female	EC	FC
41	Name 35	Female	P	FC
42	Name 36	Female	Patient	AC
43	Name 37	Male	EC	HCD
44	Name 38	Female	EC	FC
45	Name 39	Female	P	Medical - Admin
46	Name 39	Female	P	HCD
47	Name 40	Female	EC	FC
48	Name 41	Female	P	Medical - Admin
49	Name 41	Female	P	HCD
50	Name 42	Male	EC	HCD
51	Name 43	Female	Patient	AL
52	Name 44	Female	Patient	AL
53	Name 45	Male	Patient	AL
54	Name 46	Female	EC	HCD
55	Name 47	Male	IC	FC
56	Name 48	Female	P	FC
57	Name 49	Female	P	FC
58	Name 50	Male	Patient	AC
59	Name 51	Female	Patient	AC
60	Name 52	Female	Patient	AL
61	Name 53	Male	Patient	AL
62	Name 54	Female	P	FC
63	Name 55	Female	EC	FC
64	Name 56	Female	IC	FC
65	Name 57	Male	P	Management
66	Name 57	Male	P	HCD
67	Name 58	Female	EC	FC
68	Name 59	Female	Patient	AL
69	Name 60	Female	P	Medical - Admin
70	Name 60	Female	P	HCD
71	Name 61	Female	P	FC
72	Name 62	Female	P	FC
73	Name 63	Female	P	FC
74	Name 64	Female	IC	FC
75	Name 64	Female	P	Medical - Admin
76	Name 64	Female	P	HCD
77	Name 65	Male	IC	FC
78	Name 66	Female	EC	FC
79	Name 67	Female	EC	FC
80	Name 68	Female	EC	FC
81	Name 69	Female	EC	FC
82	Name 70	Male	EC	HCD
83	Name 71	Male	EC	Medical - GP
84	Name 71	Male	EC	HCD
85	Name 72	Female	EC	HCD
86	Name 73	Female	P	FC
87	Name 74	Female	EC	HCD
88	Name 75	Female	P	FC
89	Name 76	Female	EC	Medical - GP
90	Name 77	Female	EC	FC
91	Name 78	Female	P	FC
92	Name 79	Male	EC	Medical - Podiatry
93	Name 80	Female	EC	FC
Test

 

[Peter_SSs]

It looks like your workbook has been opened in an older version of Excel that does not have the UNIQUE or FILTER functions. What happens if you open it in your 365 or 2021 versions?

 

[Holger]

Tim, see if this works for you....

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Section	Total	Male	Female	Admin	Full-time	External Commuter	Internal Commuter						Name	Gender	Status	Section
2	Total Personnel	76	16	60										Name 01	Female	IC	FC
3	Personnel with Multiple Roles	10	3	7										Name 02	Male	P	Management
4	HCD	17	5	12										Name 02	Male	IC	Medical - Physiotherapy
5	FC	42	5	37		15	22	5						Name 03	Female	EC	FC
6	Medical Services	10	3	7	5	5	3	2						Name 04	Female	EC	FC
7	Patients - AC	5	1	4										Name 05	Female	EC	FC
8	Patients - AL	10	3	7										Name 06	Female	EC	FC
9														Name 06	Female	EC	HCD
10														Name 07	Female	EC	FC
11														Name 08	Male	EC	FC
12	New													Name 09	Female	Patient	AL
13	Total Personnel	76	16	60										Name 10	Female	Patient	AL
14	Personnel with Multiple Roles	10	3	7										Name 11	Male	Patient	AL
15	HCD	17	5	12										Name 12	Female	P	FC
16	FC	42	5	37		15	22	5						Name 13	Female	P	FC
17	Medical Services	10	3	7	5	5	3	2						Name 14	Female	Patient	AL
18	Patients - AC	5	1	4										Name 15	Female	P	FC
19	Patients - AL	10	3	7										Name 16	Female	IC	Medical - Podiatry
20														Name 17	Female	Patient	AC
21														Name 18	Female	P	Medical - Admin
22														Name 18	Female	P	HCD
23														Name 19	Male	EC	FC
24														Name 20	Female	EC	FC
25														Name 21	Female	EC	FC
26														Name 22	Female	EC	HCD
27														Name 23	Female	P	FC
28														Name 24	Female	EC	HCD
29														Name 25	Female	P	FC
30														Name 30	Female	P	HCD
31														Name 31	Female	EC	FC
32														Name 32	Female	Patient	AC
33														Name 33	Male	EC	FC
34														Name 34	Female	EC	FC
35														Name 35	Female	P	FC
36														Name 36	Female	Patient	AC
37														Name 37	Male	EC	HCD
38														Name 38	Female	EC	FC
39														Name 39	Female	P	Medical - Admin
40														Name 39	Female	P	HCD
41														Name 40	Female	EC	FC
42														Name 41	Female	P	Medical - Admin
43														Name 41	Female	P	HCD
44														Name 42	Male	EC	HCD
45														Name 43	Female	Patient	AL
46														Name 44	Female	Patient	AL
47														Name 45	Male	Patient	AL
48														Name 46	Female	EC	HCD
49														Name 47	Male	IC	FC
50														Name 48	Female	P	FC
51														Name 49	Female	P	FC
52														Name 50	Male	Patient	AC
53														Name 51	Female	Patient	AC
54														Name 52	Female	Patient	AL
55														Name 53	Male	Patient	AL
56														Name 54	Female	P	FC
57														Name 55	Female	EC	FC
58														Name 56	Female	IC	FC
59														Name 57	Male	P	Management
60														Name 57	Male	P	HCD
61														Name 58	Female	EC	FC
62														Name 59	Female	Patient	AL
63														Name 60	Female	P	Medical - Admin
64														Name 60	Female	P	HCD
65														Name 61	Female	P	FC
66														Name 62	Female	P	FC
67														Name 63	Female	P	FC
68														Name 64	Female	IC	FC
69														Name 64	Female	P	Medical - Admin
70														Name 64	Female	P	HCD
71														Name 65	Male	IC	FC
72														Name 66	Female	EC	FC
73														Name 67	Female	EC	FC
74														Name 68	Female	EC	FC
75														Name 69	Female	EC	FC
76														Name 70	Male	EC	HCD
77														Name 71	Male	EC	Medical - GP
78														Name 71	Male	EC	HCD
79														Name 72	Female	EC	HCD
80														Name 73	Female	P	FC
81														Name 74	Female	EC	HCD
82														Name 75	Female	P	FC
83														Name 76	Female	EC	Medical - GP
84														Name 77	Female	EC	FC
85														Name 78	Female	P	FC
86														Name 79	Male	EC	Medical - Podiatry
87														Name 80	Female	EC	FC
88
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))
C2:D2	C2	=IFERROR(ROWS(UNIQUE(FILTER(Test[Name],Test[Gender]=C1))), 0)
B3	B3	=(COUNTA(Test[Name])-(SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))))
C3:D3	C3	=(COUNTIFS(Test[Name],"*",Test[Gender],C1)-(IFERROR(ROWS(UNIQUE(FILTER(Test[Name],Test[Gender]=C1))),0)))
B4	B4	=COUNTIF(Test[Section], "HCD")
C4:D4	C4	=COUNTIFS(Test[Section], "HCD", Test[Gender], C1)
B5	B5	=COUNTIF(Test[Section], "FC")
C5:D5	C5	=COUNTIFS(Test[Section], "FC", Test[Gender], C1)
F5,F16	F5	=COUNTIFS(Test[Section], "FC", Test[Status], "P")
G5,G16	G5	=COUNTIFS(Test[Section], "FC", Test[Status], "EC")
H5,H16	H5	=COUNTIFS(Test[Section], "FC", Test[Status], "IC")
B6	B6	=COUNTIF(Test[Section], "Medical*")
C6:D6	C6	=COUNTIFS(Test[Section], "Medical*", Test[Gender], C1)
E6,E17	E6	=COUNTIFS(Test[Section], "Medical*", Test[Section], "*Admin")
F6,F17	F6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "P")
G6,G17	G6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "EC")
H6,H17	H6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "IC")
B7	B7	=COUNTIF(Test[Section], "AC")
C7:D7	C7	=COUNTIFS(Test[Section], "AC", Test[Gender], C1)
B8	B8	=COUNTIF(Test[Section], "AL")
C8:D8	C8	=COUNTIFS(Test[Section], "AL", Test[Gender], C1)
B13:B19	B13	=SUM(C13:D13)
C13:D13	C13	=COUNTA(UNIQUE(FILTER(Test[Name],Test[Gender]=C1)))
C14:D14	C14	=COUNTA(INDEX(FILTER(Test,Test[Gender]=C$1),,1))-C$13
C15:D15,D16	C15	=COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=C$1)*(Test[Section]=$A15))))
C16	C16	=COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=$C$1)*(Test[Section]=$A16))))
C17:D17	C17	=COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=C$1)*(ISNUMBER(SEARCH("*Medical*",Test[Section]))))))
C18:D18	C18	=COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=C$1)*(ISNUMBER(SEARCH("*AC*",Test[Section]))))))
C19:D19	C19	=COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=C$1)*(ISNUMBER(SEARCH("*AL?",Test[Section]))))))

 

[Holger]

...here is a slightly updated response with a bit more spilling formulas....

test2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Section	Total	Male	Female	Admin	Full-time	External Commuter	Internal Commuter						Name	Gender	Status	Section
2	Total Personnel	76	16	60										Name 01	Female	IC	FC
3	Personnel with Multiple Roles	10	3	7										Name 02	Male	P	Management
4	HCD	17	5	12										Name 02	Male	IC	Medical - Physiotherapy
5	FC	42	5	37		15	22	5						Name 03	Female	EC	FC
6	Medical Services	10	3	7	5	5	3	2						Name 04	Female	EC	FC
7	Patients - AC	5	1	4										Name 05	Female	EC	FC
8	Patients - AL	10	3	7										Name 06	Female	EC	FC
9														Name 06	Female	EC	HCD
10														Name 07	Female	EC	FC
11														Name 08	Male	EC	FC
12	New													Name 09	Female	Patient	AL
13	Total Personnel	76	16	60										Name 10	Female	Patient	AL
14	Personnel with Multiple Roles	10	3	7										Name 11	Male	Patient	AL
15	HCD	2	1	1										Name 12	Female	P	FC
16	FC	42	5	37		15	22	5						Name 13	Female	P	FC
17	Medical Services	10	3	7	5	5	3	2						Name 14	Female	Patient	AL
18	Patients - AC	5	1	4										Name 15	Female	P	FC
19	Patients - AL	10	3	7										Name 16	Female	IC	Medical - Podiatry
20														Name 17	Female	Patient	AC
21														Name 18	Female	P	Medical - Admin
22														Name 18	Female	P	HCD
23														Name 19	Male	EC	FC
24														Name 20	Female	EC	FC
25														Name 21	Female	EC	FC
26														Name 22	Female	EC	HCD
27														Name 23	Female	P	FC
28														Name 24	Female	EC	HCD
29														Name 25	Female	P	FC
30														Name 30	Female	P	HCD
31														Name 31	Female	EC	FC
32														Name 32	Female	Patient	AC
33														Name 33	Male	EC	FC
34														Name 34	Female	EC	FC
35														Name 35	Female	P	FC
36														Name 36	Female	Patient	AC
37														Name 37	Male	EC	HCD
38														Name 38	Female	EC	FC
39														Name 39	Female	P	Medical - Admin
40														Name 39	Female	P	HCD
41														Name 40	Female	EC	FC
42														Name 41	Female	P	Medical - Admin
43														Name 41	Female	P	HCD
44														Name 42	Male	EC	HCD
45														Name 43	Female	Patient	AL
46														Name 44	Female	Patient	AL
47														Name 45	Male	Patient	AL
48														Name 46	Female	EC	HCD
49														Name 47	Male	IC	FC
50														Name 48	Female	P	FC
51														Name 49	Female	P	FC
52														Name 50	Male	Patient	AC
53														Name 51	Female	Patient	AC
54														Name 52	Female	Patient	AL
55														Name 53	Male	Patient	AL
56														Name 54	Female	P	FC
57														Name 55	Female	EC	FC
58														Name 56	Female	IC	FC
59														Name 57	Male	P	Management
60														Name 57	Male	P	HCD
61														Name 58	Female	EC	FC
62														Name 59	Female	Patient	AL
63														Name 60	Female	P	Medical - Admin
64														Name 60	Female	P	HCD
65														Name 61	Female	P	FC
66														Name 62	Female	P	FC
67														Name 63	Female	P	FC
68														Name 64	Female	IC	FC
69														Name 64	Female	P	Medical - Admin
70														Name 64	Female	P	HCD
71														Name 65	Male	IC	FC
72														Name 66	Female	EC	FC
73														Name 67	Female	EC	FC
74														Name 68	Female	EC	FC
75														Name 69	Female	EC	FC
76														Name 70	Male	EC	HCD
77														Name 71	Male	EC	Medical - GP
78														Name 71	Male	EC	HCD
79														Name 72	Female	EC	HCD
80														Name 73	Female	P	FC
81														Name 74	Female	EC	HCD
82														Name 75	Female	P	FC
83														Name 76	Female	EC	Medical - GP
84														Name 77	Female	EC	FC
85														Name 78	Female	P	FC
86														Name 79	Male	EC	Medical - Podiatry
87														Name 80	Female	EC	FC
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))
C2:D2	C2	=IFERROR(ROWS(UNIQUE(FILTER(Test[Name],Test[Gender]=C1))), 0)
B3	B3	=(COUNTA(Test[Name])-(SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))))
C3:D3	C3	=(COUNTIFS(Test[Name],"*",Test[Gender],C1)-(IFERROR(ROWS(UNIQUE(FILTER(Test[Name],Test[Gender]=C1))),0)))
B4	B4	=COUNTIF(Test[Section], "HCD")
C4:D4	C4	=COUNTIFS(Test[Section], "HCD", Test[Gender], C1)
B5	B5	=COUNTIF(Test[Section], "FC")
C5:D5	C5	=COUNTIFS(Test[Section], "FC", Test[Gender], C1)
F5,F16	F5	=COUNTIFS(Test[Section], "FC", Test[Status], "P")
G5,G16	G5	=COUNTIFS(Test[Section], "FC", Test[Status], "EC")
H5,H16	H5	=COUNTIFS(Test[Section], "FC", Test[Status], "IC")
B6	B6	=COUNTIF(Test[Section], "Medical*")
C6:D6	C6	=COUNTIFS(Test[Section], "Medical*", Test[Gender], C1)
E6,E17	E6	=COUNTIFS(Test[Section], "Medical*", Test[Section], "*Admin")
F6,F17	F6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "P")
G6,G17	G6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "EC")
H6,H17	H6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "IC")
B7	B7	=COUNTIF(Test[Section], "AC")
C7:D7	C7	=COUNTIFS(Test[Section], "AC", Test[Gender], C1)
B8	B8	=COUNTIF(Test[Section], "AL")
C8:D8	C8	=COUNTIFS(Test[Section], "AL", Test[Gender], C1)
A13:D19	A13	=LET( line1,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],Test[Gender]=c))))), line2,BYCOL(C1:D1,LAMBDA(c,COUNTA(INDEX(FILTER(Test,Test[Gender]=c),,1))))-line1, line3,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(Test[Section]="HDC")))))), line4,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(Test[Section]="FC")))))), line5,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(ISNUMBER(SEARCH("*Medical*",Test[Section])))))))), line6,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(ISNUMBER(SEARCH("*AC*",Test[Section])))))))), line7,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(ISNUMBER(SEARCH("*AL?",Test[Section])))))))), HSTACK( VSTACK(A2:A8), VSTACK(SUM(line1),SUM(line2),SUM(line3),SUM(line4),SUM(line5),SUM(line6),SUM(line7)), VSTACK(line1,line2,line3,line4,line5,line6,line7) ) )
Dynamic array formulas.

 

[Holger]

hi,

please ignore prior version, as I had a typo in line 3.

test2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Section	Total	Male	Female	Admin	Full-time	External Commuter	Internal Commuter						Name	Gender	Status	Section
2	Total Personnel	76	16	60										Name 01	Female	IC	FC
3	Personnel with Multiple Roles	10	3	7										Name 02	Male	P	Management
4	HCD	17	5	12										Name 02	Male	IC	Medical - Physiotherapy
5	FC	42	5	37		15	22	5						Name 03	Female	EC	FC
6	Medical Services	10	3	7	5	5	3	2						Name 04	Female	EC	FC
7	Patients - AC	5	1	4										Name 05	Female	EC	FC
8	Patients - AL	10	3	7										Name 06	Female	EC	FC
9														Name 06	Female	EC	HCD
10														Name 07	Female	EC	FC
11														Name 08	Male	EC	FC
12	New													Name 09	Female	Patient	AL
13	Total Personnel	76	16	60										Name 10	Female	Patient	AL
14	Personnel with Multiple Roles	10	3	7										Name 11	Male	Patient	AL
15	HCD	17	5	12										Name 12	Female	P	FC
16	FC	42	5	37		15	22	5						Name 13	Female	P	FC
17	Medical Services	10	3	7	5	5	3	2						Name 14	Female	Patient	AL
18	Patients - AC	5	1	4										Name 15	Female	P	FC
19	Patients - AL	10	3	7										Name 16	Female	IC	Medical - Podiatry
20														Name 17	Female	Patient	AC
21														Name 18	Female	P	Medical - Admin
22														Name 18	Female	P	HCD
23														Name 19	Male	EC	FC
24														Name 20	Female	EC	FC
25														Name 21	Female	EC	FC
26														Name 22	Female	EC	HCD
27														Name 23	Female	P	FC
28														Name 24	Female	EC	HCD
29														Name 25	Female	P	FC
30														Name 30	Female	P	HCD
31														Name 31	Female	EC	FC
32														Name 32	Female	Patient	AC
33														Name 33	Male	EC	FC
34														Name 34	Female	EC	FC
35														Name 35	Female	P	FC
36														Name 36	Female	Patient	AC
37														Name 37	Male	EC	HCD
38														Name 38	Female	EC	FC
39														Name 39	Female	P	Medical - Admin
40														Name 39	Female	P	HCD
41														Name 40	Female	EC	FC
42														Name 41	Female	P	Medical - Admin
43														Name 41	Female	P	HCD
44														Name 42	Male	EC	HCD
45														Name 43	Female	Patient	AL
46														Name 44	Female	Patient	AL
47														Name 45	Male	Patient	AL
48														Name 46	Female	EC	HCD
49														Name 47	Male	IC	FC
50														Name 48	Female	P	FC
51														Name 49	Female	P	FC
52														Name 50	Male	Patient	AC
53														Name 51	Female	Patient	AC
54														Name 52	Female	Patient	AL
55														Name 53	Male	Patient	AL
56														Name 54	Female	P	FC
57														Name 55	Female	EC	FC
58														Name 56	Female	IC	FC
59														Name 57	Male	P	Management
60														Name 57	Male	P	HCD
61														Name 58	Female	EC	FC
62														Name 59	Female	Patient	AL
63														Name 60	Female	P	Medical - Admin
64														Name 60	Female	P	HCD
65														Name 61	Female	P	FC
66														Name 62	Female	P	FC
67														Name 63	Female	P	FC
68														Name 64	Female	IC	FC
69														Name 64	Female	P	Medical - Admin
70														Name 64	Female	P	HCD
71														Name 65	Male	IC	FC
72														Name 66	Female	EC	FC
73														Name 67	Female	EC	FC
74														Name 68	Female	EC	FC
75														Name 69	Female	EC	FC
76														Name 70	Male	EC	HCD
77														Name 71	Male	EC	Medical - GP
78														Name 71	Male	EC	HCD
79														Name 72	Female	EC	HCD
80														Name 73	Female	P	FC
81														Name 74	Female	EC	HCD
82														Name 75	Female	P	FC
83														Name 76	Female	EC	Medical - GP
84														Name 77	Female	EC	FC
85														Name 78	Female	P	FC
86														Name 79	Male	EC	Medical - Podiatry
87														Name 80	Female	EC	FC
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))
C2:D2	C2	=IFERROR(ROWS(UNIQUE(FILTER(Test[Name],Test[Gender]=C1))), 0)
B3	B3	=(COUNTA(Test[Name])-(SUMPRODUCT(1/COUNTIFS(Test[Name], Test[Name]))))
C3:D3	C3	=(COUNTIFS(Test[Name],"*",Test[Gender],C1)-(IFERROR(ROWS(UNIQUE(FILTER(Test[Name],Test[Gender]=C1))),0)))
B4	B4	=COUNTIF(Test[Section], "HCD")
C4:D4	C4	=COUNTIFS(Test[Section], "HCD", Test[Gender], C1)
B5	B5	=COUNTIF(Test[Section], "FC")
C5:D5	C5	=COUNTIFS(Test[Section], "FC", Test[Gender], C1)
F5,F16	F5	=COUNTIFS(Test[Section], "FC", Test[Status], "P")
G5,G16	G5	=COUNTIFS(Test[Section], "FC", Test[Status], "EC")
H5,H16	H5	=COUNTIFS(Test[Section], "FC", Test[Status], "IC")
B6	B6	=COUNTIF(Test[Section], "Medical*")
C6:D6	C6	=COUNTIFS(Test[Section], "Medical*", Test[Gender], C1)
E6,E17	E6	=COUNTIFS(Test[Section], "Medical*", Test[Section], "*Admin")
F6,F17	F6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "P")
G6,G17	G6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "EC")
H6,H17	H6	=COUNTIFS(Test[Section], "Medical*", Test[Status], "IC")
B7	B7	=COUNTIF(Test[Section], "AC")
C7:D7	C7	=COUNTIFS(Test[Section], "AC", Test[Gender], C1)
B8	B8	=COUNTIF(Test[Section], "AL")
C8:D8	C8	=COUNTIFS(Test[Section], "AL", Test[Gender], C1)
A13:D19	A13	=LET( line1,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],Test[Gender]=c))))), line2,BYCOL(C1:D1,LAMBDA(c,COUNTA(INDEX(FILTER(Test,Test[Gender]=c),,1))))-line1, line3,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(Test[Section]="HCD")))))), line4,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(Test[Section]="FC")))))), line5,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(ISNUMBER(SEARCH("*Medical*",Test[Section])))))))), line6,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(ISNUMBER(SEARCH("*AC*",Test[Section])))))))), line7,BYCOL(C1:D1,LAMBDA(c,COUNTA(UNIQUE(FILTER(Test[Name],(Test[Gender]=c)*(ISNUMBER(SEARCH("*AL?",Test[Section])))))))), HSTACK( VSTACK(A2:A8), VSTACK(SUM(line1),SUM(line2),SUM(line3),SUM(line4),SUM(line5),SUM(line6),SUM(line7)), VSTACK(line1,line2,line3,line4,line5,line6,line7) ) )
Dynamic array formulas.