# Counting names based on their department



## Nutmegger (Dec 14, 2022)

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 (Dec 14, 2022)

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 (Dec 14, 2022)

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.xlsxABCDEFGHIJKLMNO1SunMonTueWedThurFriSatNamesDepartment2Alfonso TorresAccounting3I would like to automate this  part:Arabella VaughanHR4Beckett FergusonHR5Representation by deptNumbersCharlee BlackwellBroderick GalvanService6HR12Elise BarryBrynn ScottHR7Accounting6Carly NobleTalent8Sales9Carolyn IbarraService9Service11Carolyn IbarraCharlee BlackwellHR10Cristian HunterHR11Hayley AveryArabella VaughanYahir BenderDamion RushAccounting12Makayla VazquezJake DickersonCristian HunterDemarion MorganHR13Gavin PotterShawn OwensElise BarryAccounting14Isla VanceJoselyn RiosEsmeralda OrrService15Hayley AveryDemarion MorganDemarion MorganGavin PotterService16Kayden LeonJaylin McphersonGeovanni AllisonSales17Katie YatesWade MullenHayden CoffeySales18Scarlett WeaverHayley AverySales19Peyton HarveyHazel CaseyAccounting20Isla VanceService21Broderick GalvanJadyn GoldenHR22Geovanni AllisonJake DickersonAccounting23Hazel CaseyJavier BishopService24Matias RichardJayda SweeneyHR25Johnathan ShepardJaylin McphersonAccounting26Charlee BlackwellJohnathan ShepardHR27Joselyn RiosTalent28Kaitlin UnderwoodHR29Katie YatesSales30Kayden LeonService31Keith FinleyService32Maggie OnealHR33Makayla VazquezHR34Marlon McmahonHR35Matias RichardSales36Natalia MejiaService37Peyton HarveySales38Reina DavidsonAccounting39Rory MorrowSales40Ryder HoltHR41Sanai MataHR42Scarlett WeaverAccounting43Shawn OwensAccounting44Shelby BurnettSales45Talon AustinSales46Terry MolinaService47Thomas SuttonAccounting48Tomas LiuSales49Ty McintyreHR50Wade MullenSales51Yahir BenderSales52Sheet1


----------



## kevin9999 (Dec 15, 2022)

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.xlsxABCDEFGHIJKLMN1SunMonTueWedThurFriSatNamesDepartment2Alfonso TorresAccounting3I would like to automate this  part:Arabella VaughanHR4Beckett FergusonHR5Representation by deptNumbersCharlee BlackwellBroderick GalvanService6HR8HRElise BarryBrynn ScottHR7Accounting6AccountingCarly NobleTalent8Sales8ServiceCarolyn IbarraService9Service5SalesCarolyn IbarraCharlee BlackwellHR10HRCristian HunterHR11SalesHayley AveryArabella VaughanYahir BenderDamion RushAccounting12HRMakayla VazquezJake DickersonCristian HunterDemarion MorganHR13AccountingGavin PotterShawn OwensElise BarryAccounting14HRIsla VanceJoselyn RiosEsmeralda OrrService15ServiceHayley AveryDemarion MorganDemarion MorganGavin PotterService16AccountingKayden LeonJaylin McphersonGeovanni AllisonSales17ServiceKatie YatesWade MullenHayden CoffeySales18TalentScarlett WeaverHayley AverySales19SalesPeyton HarveyHazel CaseyAccounting20HRIsla VanceService21HRBroderick GalvanJadyn GoldenHR22ServiceGeovanni AllisonJake DickersonAccounting23AccountingHazel CaseyJavier BishopService24SalesMatias RichardJayda SweeneyHR25SalesJohnathan ShepardJaylin McphersonAccounting26AccountingCharlee BlackwellJohnathan ShepardHR27SalesJoselyn RiosTalent28ServiceKaitlin UnderwoodHR29SalesKatie YatesSales30AccountingKayden LeonService31SalesKeith FinleyService32HRMaggie OnealHR33HRMakayla VazquezHR34Marlon McmahonHR35Matias RichardSales36Natalia MejiaService37Peyton HarveySales38Reina DavidsonAccounting39Rory MorrowSales40Ryder HoltHR41Sanai MataHR42Scarlett WeaverAccounting43Shawn OwensAccounting44Shelby BurnettSales45Talon AustinSales46Terry MolinaService47Thomas SuttonAccounting48Tomas LiuSales49Ty McintyreHR50Wade MullenSales51Yahir BenderSales52Sheet1Cell FormulasRangeFormulaC6:C33C6=VLOOKUP(TOCOL($D$2:$J$36,1),$M$2:$N$51,2,FALSE)B6:B9B6=COUNTIF($C$6#,A6)Dynamic array formulas.


----------



## Fluff (Dec 15, 2022)

Another option
Fluff.xlsmABCDEFGHIJKLMN1SunMonTueWedThurFriSatNamesDepartment2Alfonso TorresAccounting3I would like to automate this  part:Arabella VaughanHR4Beckett FergusonHR5Representation by deptNumbersCharlee BlackwellBroderick GalvanService6HR8Elise BarryBrynn ScottHR7Accounting6Carly NobleTalent8Sales8Carolyn IbarraService9Service5Carolyn IbarraCharlee BlackwellHR10Cristian HunterHR11Hayley AveryArabella VaughanYahir BenderDamion RushAccounting12Makayla VazquezJake DickersonCristian HunterDemarion MorganHR13Gavin PotterShawn OwensElise BarryAccounting14Isla VanceJoselyn RiosEsmeralda OrrService15Hayley AveryDemarion MorganDemarion MorganGavin PotterService16Kayden LeonJaylin McphersonGeovanni AllisonSales17Katie YatesWade MullenHayden CoffeySales18Scarlett WeaverHayley AverySales19Peyton HarveyHazel CaseyAccounting20Isla VanceService21Broderick GalvanJadyn GoldenHR22Geovanni AllisonJake DickersonAccounting23Hazel CaseyJavier BishopService24Matias RichardJayda SweeneyHR25Johnathan ShepardJaylin McphersonAccounting26Charlee BlackwellJohnathan ShepardHR27Joselyn RiosTalent28Kaitlin UnderwoodHR29Katie YatesSales30Kayden LeonService31Keith FinleyService32Maggie OnealHR33Makayla VazquezHR34Marlon McmahonHR35Matias RichardSales36Natalia MejiaService37Peyton HarveySales38Reina DavidsonAccounting39Rory MorrowSales40Ryder HoltHR41Sanai MataHR42Scarlett WeaverAccounting43Shawn OwensAccounting44Shelby BurnettSales45Talon AustinSales46Terry MolinaService47Thomas SuttonAccounting48Tomas LiuSales49Ty McintyreHR50Wade MullenSales51Yahir BenderSalesMainCell FormulasRangeFormulaB6:B9B6=SUM(COUNTIFS(M:M,TOCOL($D$2:$J$30,1),N:N,A6))


----------



## shinigamilight (Dec 15, 2022)

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 (Dec 16, 2022)

Fluff said:


> 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.xlsxABCDEFGHIJKLMNO1SunMonTueWedThurFriSatNamesDepartment2Alfonso TorresAccounting3I would like to automate this  part:Arabella VaughanHR4Beckett FergusonHR5Representation by deptNumbersCharlee BlackwellBroderick GalvanService6HR0Elise BarryBrynn ScottHR7Accounting0Carly NobleTalent8Sales0Cristian HunterCarolyn IbarraService9Service0Carolyn IbarraCharlee BlackwellHR10Cristian HunterHR11Hayley AveryArabella VaughanYahir BenderDamion RushAccounting12Makayla VazquezJake DickersonCristian HunterDemarion MorganHR13Gavin PotterShawn OwensElise BarryAccounting14Isla VanceJoselyn RiosEsmeralda OrrService15Hayley AveryDemarion MorganDemarion MorganGavin PotterService16Kayden LeonJaylin McphersonGeovanni AllisonSales17Katie YatesWade MullenHayden CoffeySales18Scarlett WeaverHayley AverySales19Peyton HarveyHazel CaseyAccounting20Isla VanceService21Broderick GalvanJadyn GoldenHR22Geovanni AllisonJake DickersonAccounting23Hazel CaseyJavier BishopService24Matias RichardJayda SweeneyHR25Johnathan ShepardJaylin McphersonAccounting26Charlee BlackwellJohnathan ShepardHR27Joselyn RiosTalent28Kaitlin UnderwoodHR29Katie YatesSales30Kayden LeonService31Keith FinleyService32Maggie OnealHR33Makayla VazquezHR34Marlon McmahonHR35Matias RichardSales36Natalia MejiaService37Peyton HarveySales38Reina DavidsonAccounting39Rory MorrowSales40Ryder HoltHR41Sanai MataHR42Scarlett WeaverAccounting43Shawn OwensAccounting44Shelby BurnettSales45Talon AustinSales46Terry MolinaService47Thomas SuttonAccounting48Tomas LiuSales49Ty McintyreHR50Wade MullenSales51Yahir BenderSales52Sheet1Cell FormulasRangeFormulaB6:B9B6=SUM(COUNTIFS(M:M,TOCOL($D$2:$J$30,1),N:N,A6))


----------



## Fluff (Dec 16, 2022)

Check that you have the TOCOL function.


----------



## Nutmegger (Dec 16, 2022)

Fluff said:


> 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 (Dec 16, 2022)

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


----------



## Nutmegger (Dec 14, 2022)

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 (Dec 16, 2022)

kevin9999 said:


> 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.xlsxABCDEFGHIJKLMN1SunMonTueWedThurFriSatNamesDepartment2Alfonso TorresAccounting3I would like to automate this  part:Arabella VaughanHR4Beckett FergusonHR5Representation by deptNumbersCharlee BlackwellBroderick GalvanService6HR8HRElise BarryBrynn ScottHR7Accounting6AccountingCarly NobleTalent8Sales8ServiceCarolyn IbarraService9Service5SalesCarolyn IbarraCharlee BlackwellHR10HRCristian HunterHR11SalesHayley AveryArabella VaughanYahir BenderDamion RushAccounting12HRMakayla VazquezJake DickersonCristian HunterDemarion MorganHR13AccountingGavin PotterShawn OwensElise BarryAccounting14HRIsla VanceJoselyn RiosEsmeralda OrrService15ServiceHayley AveryDemarion MorganDemarion MorganGavin PotterService16AccountingKayden LeonJaylin McphersonGeovanni AllisonSales17ServiceKatie YatesWade MullenHayden CoffeySales18TalentScarlett WeaverHayley AverySales19SalesPeyton HarveyHazel CaseyAccounting20HRIsla VanceService21HRBroderick GalvanJadyn GoldenHR22ServiceGeovanni AllisonJake DickersonAccounting23AccountingHazel CaseyJavier BishopService24SalesMatias RichardJayda SweeneyHR25SalesJohnathan ShepardJaylin McphersonAccounting26AccountingCharlee BlackwellJohnathan ShepardHR27SalesJoselyn RiosTalent28ServiceKaitlin UnderwoodHR29SalesKatie YatesSales30AccountingKayden LeonService31SalesKeith FinleyService32HRMaggie OnealHR33HRMakayla VazquezHR34Marlon McmahonHR35Matias RichardSales36Natalia MejiaService37Peyton HarveySales38Reina DavidsonAccounting39Rory MorrowSales40Ryder HoltHR41Sanai MataHR42Scarlett WeaverAccounting43Shawn OwensAccounting44Shelby BurnettSales45Talon AustinSales46Terry MolinaService47Thomas SuttonAccounting48Tomas LiuSales49Ty McintyreHR50Wade MullenSales51Yahir BenderSales52Sheet1Cell FormulasRangeFormulaC6:C33C6=VLOOKUP(TOCOL($D$2:$J$36,1),$M$2:$N$51,2,FALSE)B6:B9B6=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 (Dec 16, 2022)

Fluff said:


> 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 (Dec 16, 2022)

Fluff said:


> 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 (Dec 16, 2022)

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 (Dec 16, 2022)

Fluff said:


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


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 (Dec 16, 2022)

Glad we could help & thanks for the feedback.


----------

