Count Employees per Manager without duplicates

juanma2550

New Member
Joined
Nov 16, 2014
Messages
2
Hello!

I have some doubts about how to calculate this results.

Without using a pivot table, I need to know how many employees each manager has. For example: If I enter the name: "Zara" on the cell G1 I'll need to get the number 5 on the cell H1, because Zara has 5 employees even though there are 10 rows if you filter by the Manager Zara.

Note that there are some employees with the same name, like "John" but there are two different Johns, one is a Zara's employee, and the other one is Louise's employee, and also the ID is different.:banghead:

So, basically I need a formula that calculates how many employees have each manager without counting the duplicates and keeping in mind there are some employees with the same name but different manager. I really do not know how to do that.:confused:

Please help!!:rolleyes:

Employee
ManagerIDDayStartsLeave
JohnZara65896Monday09:0017:00
JohnZara65896Tuesday09:0017:00
JohnLouise25368Wednesday09:0017:00
JohnLouise25368Thursday09:0017:00
DavidLouise69985Friday09:0017:00
DavidLouise69985Saturday09:0017:00
DavidZara45896Sunday09:0017:00
DavidZara45896Monday09:0017:00
DavidAlex65471Tuesday09:0017:00
SteveAlex23698Wednesday09:0017:00
SteveZara23698Thursday09:0017:00
MarkLouise44589Friday09:0017:00
MarkAlex44779Saturday09:0017:00
MarkLouise44589Sunday09:0017:00
MartinLouise14589Monday09:0017:00
MartinLouise14589Tuesday09:0017:00
MartinZara97879Wednesday09:0017:00
MartinAlex45897Thursday09:0017:00
MartinAlex45897Friday09:0017:00
JohnZara65896Saturday09:0017:00
JohnZara65896Sunday09:0017:00
JohnLouise25368Monday09:0017:00
JohnAlex99866Tuesday09:0017:00
RalphZara87898Wednesday09:0017:00
RalphZara64796Thursday09:0017:00

<tbody>
</tbody>


Thank you very much!!:pray:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
G1: Zara (a manager of interest)

H1, control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF($A$2:$A$26<>"",IF($B$2:$B$26=$G1,
  MATCH("~"&$A$2:$A$26,$A$2:$A$26&"",0))),
  ROW($A$2:$A$26)-ROW($A$2)+1),1))
 

juanma2550

New Member
Joined
Nov 16, 2014
Messages
2
G1: Zara (a manager of interest)

H1, control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF($A$2:$A$26<>"",IF($B$2:$B$26=$G1,
  MATCH("~"&$A$2:$A$26,$A$2:$A$26&"",0))),
  ROW($A$2:$A$26)-ROW($A$2)+1),1))
Thank you very much!!! It does work, but, can you please explain me how does it work, I'd like to understand so next time I can do it myself :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top