Count Employees per Manager without duplicates

J

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!!:help:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 70, align: center"]Employee
[/TD]
[TD="width: 63, align: center"]Manager[/TD]
[TD="width: 42, align: center"]ID[/TD]
[TD="width: 76, align: center"]Day[/TD]
[TD="width: 43, align: center"]Starts[/TD]
[TD="width: 46, align: center"]Leave[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]65896[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]65896[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]25368[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]25368[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]David[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]69985[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]David[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]69985[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]David[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]45896[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]David[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]45896[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]David[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]65471[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]23698[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Steve[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]23698[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Mark[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]44589[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Mark[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]44779[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Mark[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]44589[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Martin[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]14589[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Martin[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]14589[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Martin[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]97879[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Martin[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]45897[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Martin[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]45897[/TD]
[TD="align: center"]Friday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]65896[/TD]
[TD="align: center"]Saturday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]65896[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Louise[/TD]
[TD="align: center"]25368[/TD]
[TD="align: center"]Monday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]John[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]99866[/TD]
[TD="align: center"]Tuesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Ralph[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]87898[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]Ralph[/TD]
[TD="align: center"]Zara[/TD]
[TD="align: center"]64796[/TD]
[TD="align: center"]Thursday[/TD]
[TD="align: center"]09:00[/TD]
[TD="align: center"]17:00[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much!!:pray:
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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))
 
Upvote 0
Aladin Akyurek said:
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))
Click to expand...

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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
93
HighAndWilder
H
N
  • Question Question
VBA using INDEX and MATCH based on cell colour
Replies
0
Views
454
Nashpotatoes
N
G
  • Solved
Formula - Sum worked hours based on shift timing
Replies
6
Views
876
Toadstool
Toadstool
D
  • Question Question
VBA/Formula to lock cells when criteria is met
Replies
1
Views
282
mabbutt
M
Agnarr
  • Question Question
Log. Need to work on g.sheets
Replies
0
Views
306
Agnarr
Agnarr

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top