Counting names based on their department

[Nutmegger]

Hi all,
I have a workbook where people's names are entered into (A2:U30)
The names that are allowed to be entered are validated against a list of names in (G1:G50);
Each name has a corresponding department in (H1:H50); There are 4 different departments, so each person belongs to 1 of the 4 departments.

I am looking for a formula that will give me a count of how many people entered in (A2:U30) represent each of the 4 departments.
In other words, I want to get 4 formulas in 4 cells showing something like this:

Accounting: 5
Sales: 12
Service: 20
HR: 8

I hope someone can help - thank you in advance!

 

[Nutmegger]

There's probably a more elegant solution than this, but if you're OK using a helper column (I've used column C here but it could be anywhere there's a clear column, and you can hide it if you want) then the following seems to work for what you want. I note you have 365 so hopefully the TOCOL() function is available to you. Enter the VLOOKUP/TOCOL formula into cell C6, and then a straightforward COUNTIF() referencing the spill range in B6:B9.

count.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Sun	Mon	Tue	Wed	Thur	Fri	Sat			Names	Department
2													Alfonso Torres	Accounting
3	I would like to automate this part:												Arabella Vaughan	HR
4													Beckett Ferguson	HR
5	Representation by dept	Numbers							Charlee Blackwell				Broderick Galvan	Service
6	HR	8	HR		Elise Barry								Brynn Scott	HR
7	Accounting	6	Accounting										Carly Noble	Talent
8	Sales	8	Service										Carolyn Ibarra	Service
9	Service	5	Sales	Carolyn Ibarra									Charlee Blackwell	HR
10			HR										Cristian Hunter	HR
11			Sales		Hayley Avery	Arabella Vaughan		Yahir Bender					Damion Rush	Accounting
12			HR			Makayla Vazquez		Jake Dickerson		Cristian Hunter			Demarion Morgan	HR
13			Accounting			Gavin Potter		Shawn Owens					Elise Barry	Accounting
14			HR			Isla Vance		Joselyn Rios					Esmeralda Orr	Service
15			Service	Hayley Avery				Demarion Morgan		Demarion Morgan			Gavin Potter	Service
16			Accounting	Kayden Leon				Jaylin Mcpherson					Geovanni Allison	Sales
17			Service	Katie Yates				Wade Mullen					Hayden Coffey	Sales
18			Talent	Scarlett Weaver									Hayley Avery	Sales
19			Sales	Peyton Harvey									Hazel Casey	Accounting
20			HR										Isla Vance	Service
21			HR				Broderick Galvan						Jadyn Golden	HR
22			Service				Geovanni Allison						Jake Dickerson	Accounting
23			Accounting				Hazel Casey						Javier Bishop	Service
24			Sales				Matias Richard						Jayda Sweeney	HR
25			Sales				Johnathan Shepard						Jaylin Mcpherson	Accounting
26			Accounting				Charlee Blackwell						Johnathan Shepard	HR
27			Sales										Joselyn Rios	Talent
28			Service										Kaitlin Underwood	HR
29			Sales										Katie Yates	Sales
30			Accounting										Kayden Leon	Service
31			Sales										Keith Finley	Service
32			HR										Maggie Oneal	HR
33			HR										Makayla Vazquez	HR
34													Marlon Mcmahon	HR
35													Matias Richard	Sales
36													Natalia Mejia	Service
37													Peyton Harvey	Sales
38													Reina Davidson	Accounting
39													Rory Morrow	Sales
40													Ryder Holt	HR
41													Sanai Mata	HR
42													Scarlett Weaver	Accounting
43													Shawn Owens	Accounting
44													Shelby Burnett	Sales
45													Talon Austin	Sales
46													Terry Molina	Service
47													Thomas Sutton	Accounting
48													Tomas Liu	Sales
49													Ty Mcintyre	HR
50													Wade Mullen	Sales
51													Yahir Bender	Sales
52
Sheet1

Cell Formulas
Range	Formula
C6:C33	C6	=VLOOKUP(TOCOL($D$2:$J$36,1),$M$2:$N$51,2,FALSE)
B6:B9	B6	=COUNTIF($C$6#,A6)
Dynamic array formulas.

Sigh, a helper column wouldn't work, because I am trying to count the names from each department in the D2:N30 array of names, where a name from any department could be in any cell in that array..

 

[Nutmegger]

That formula works for me. If you type =to into a cell, do you get 3 possible options, or just 1?

If I type =to into B6, all I get is the "Today" formula suggestion...
Also showing screenshots where the =SUM(COUNTIFS(M:M,TOCOL($D$2:$J$30,1),N:N,A6)) formula is in that cell, shows no errors, but still calculates as a "0":

 

[Nutmegger]

That formula works for me. If you type =to into a cell, do you get 3 possible options, or just 1?

I see what you mean... if I type =to into a cell, I don't get TOCOL as a suggested formula.
Why wouldn't I have it?
My excel is Windows 64-bit based:

Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20806) 64-bit

 

[Fluff]

Ok, you may be on the semi annual channel which doesn't have that function yet.
Try

=LET(Data,$D$2:$J$30,r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),x,INDEX(Data,MOD(s,r)+1,INT(s/r)+1),SUM(COUNTIFS(M:M,FILTER(x,x<>""),N:N,A6)))

 

[Nutmegger]

Ok, you may be on the semi annual channel which doesn't have that function yet.
Try

=LET(Data,$D$2:$J$30,r,ROWS(Data),s,SEQUENCE(r*COLUMNS(Data),,0),x,INDEX(Data,MOD(s,r)+1,INT(s/r)+1),SUM(COUNTIFS(M:M,FILTER(x,x<>""),N:N,A6)))

You are a genius. Thank you, thank you, thank you.
A much longer formula, but it sure works. Can't wait for the TOCOL to arrive.
I really appreciate your help!

 

[Fluff]

Glad we could help & thanks for the feedback.