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!

 

[Fluff]

Hi & welcome to MrExcel.
Can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Nutmegger]

Hi,
Re-writing the coordinates a bit, and attaching a sample:
I have a workbook where people's names are entered into (D2:J30)
The names that are allowed to be entered are validated against a list of names in (M2:M51) (Validation not used in the sample spreadsheet)
Each name has a corresponding department listed in (N2:N51); 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 (D2:J30) 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

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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	12			Elise Barry								Brynn Scott	HR
7	Accounting	6											Carly Noble	Talent
8	Sales	9											Carolyn Ibarra	Service
9	Service	11		Carolyn Ibarra									Charlee Blackwell	HR
10													Cristian Hunter	HR
11					Hayley Avery	Arabella Vaughan		Yahir Bender					Damion Rush	Accounting
12						Makayla Vazquez		Jake Dickerson		Cristian Hunter			Demarion Morgan	HR
13						Gavin Potter		Shawn Owens					Elise Barry	Accounting
14						Isla Vance		Joselyn Rios					Esmeralda Orr	Service
15				Hayley Avery				Demarion Morgan		Demarion Morgan			Gavin Potter	Service
16				Kayden Leon				Jaylin Mcpherson					Geovanni Allison	Sales
17				Katie Yates				Wade Mullen					Hayden Coffey	Sales
18				Scarlett Weaver									Hayley Avery	Sales
19				Peyton Harvey									Hazel Casey	Accounting
20													Isla Vance	Service
21							Broderick Galvan						Jadyn Golden	HR
22							Geovanni Allison						Jake Dickerson	Accounting
23							Hazel Casey						Javier Bishop	Service
24							Matias Richard						Jayda Sweeney	HR
25							Johnathan Shepard						Jaylin Mcpherson	Accounting
26							Charlee Blackwell						Johnathan Shepard	HR
27													Joselyn Rios	Talent
28													Kaitlin Underwood	HR
29													Katie Yates	Sales
30													Kayden Leon	Service
31													Keith Finley	Service
32													Maggie Oneal	HR
33													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

 

[kevin9999]

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.

 

[Fluff]

Another option

Fluff.xlsm
	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			Elise Barry								Brynn Scott	HR
7	Accounting	6											Carly Noble	Talent
8	Sales	8											Carolyn Ibarra	Service
9	Service	5		Carolyn Ibarra									Charlee Blackwell	HR
10													Cristian Hunter	HR
11					Hayley Avery	Arabella Vaughan		Yahir Bender					Damion Rush	Accounting
12						Makayla Vazquez		Jake Dickerson		Cristian Hunter			Demarion Morgan	HR
13						Gavin Potter		Shawn Owens					Elise Barry	Accounting
14						Isla Vance		Joselyn Rios					Esmeralda Orr	Service
15				Hayley Avery				Demarion Morgan		Demarion Morgan			Gavin Potter	Service
16				Kayden Leon				Jaylin Mcpherson					Geovanni Allison	Sales
17				Katie Yates				Wade Mullen					Hayden Coffey	Sales
18				Scarlett Weaver									Hayley Avery	Sales
19				Peyton Harvey									Hazel Casey	Accounting
20													Isla Vance	Service
21							Broderick Galvan						Jadyn Golden	HR
22							Geovanni Allison						Jake Dickerson	Accounting
23							Hazel Casey						Javier Bishop	Service
24							Matias Richard						Jayda Sweeney	HR
25							Johnathan Shepard						Jaylin Mcpherson	Accounting
26							Charlee Blackwell						Johnathan Shepard	HR
27													Joselyn Rios	Talent
28													Kaitlin Underwood	HR
29													Katie Yates	Sales
30													Kayden Leon	Service
31													Keith Finley	Service
32													Maggie Oneal	HR
33													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
Main

Cell Formulas
Range	Formula
B6:B9	B6	=SUM(COUNTIFS(M:M,TOCOL($D$2:$J$30,1),N:N,A6))

 

[shinigamilight]

I don't think it's possible with older version of excels but definitely with vba.

Sub testing1()
            Dim j, k, i As Integer
            Dim store As String
            For j = 6 To 9
                    store = 0
                    For k = 2 To 26
                                For i = 4 To 10
                                        If Cells(k, i) <> "" Then
                                                If WorksheetFunction.VLookup(Cells(k, i), Range("M:N"), 2, 0) = Range("A" & j) Then
                                                    store = store + 1
                                                    Cells(j, 2) = store
                                                End If
                                        End If
                                Next i
                    Next k
            Next j
End Sub

 

[Nutmegger]

Another option

I like your formula best, I think

"=SUM(COUNTIFS(M:M,TOCOL($D$2:$J$30,1),N:N,A6))"
.... but it is not counting... the result comes up as a "0"
What did I miss?

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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	0			Elise Barry								Brynn Scott	HR
7	Accounting	0											Carly Noble	Talent
8	Sales	0					Cristian Hunter						Carolyn Ibarra	Service
9	Service	0		Carolyn Ibarra									Charlee Blackwell	HR
10													Cristian Hunter	HR
11					Hayley Avery	Arabella Vaughan		Yahir Bender					Damion Rush	Accounting
12						Makayla Vazquez		Jake Dickerson		Cristian Hunter			Demarion Morgan	HR
13						Gavin Potter		Shawn Owens					Elise Barry	Accounting
14						Isla Vance		Joselyn Rios					Esmeralda Orr	Service
15				Hayley Avery				Demarion Morgan		Demarion Morgan			Gavin Potter	Service
16				Kayden Leon				Jaylin Mcpherson					Geovanni Allison	Sales
17				Katie Yates				Wade Mullen					Hayden Coffey	Sales
18				Scarlett Weaver									Hayley Avery	Sales
19				Peyton Harvey									Hazel Casey	Accounting
20													Isla Vance	Service
21							Broderick Galvan						Jadyn Golden	HR
22							Geovanni Allison						Jake Dickerson	Accounting
23							Hazel Casey						Javier Bishop	Service
24							Matias Richard						Jayda Sweeney	HR
25							Johnathan Shepard						Jaylin Mcpherson	Accounting
26							Charlee Blackwell						Johnathan Shepard	HR
27													Joselyn Rios	Talent
28													Kaitlin Underwood	HR
29													Katie Yates	Sales
30													Kayden Leon	Service
31													Keith Finley	Service
32													Maggie Oneal	HR
33													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
B6:B9	B6	=SUM(COUNTIFS(M:M,TOCOL($D$2:$J$30,1),N:N,A6))

 

[Fluff]

Check that you have the TOCOL function.

 

[Nutmegger]

Check that you have the TOCOL function.

Yep, that's what I have for the formula in B6, exactly like this:
=SUM(COUNTIFS(M:M,TOCOL($D$2:$J$30,1),N:N,A6))
But it keeps showing a "0" in the cell

 

[Fluff]

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