Count Employees per Manager without duplicates

[juanma2550]

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.

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.

Please help!!

Employee	Manager	ID	Day	Starts	Leave
John	Zara	65896	Monday	09:00	17:00
John	Zara	65896	Tuesday	09:00	17:00
John	Louise	25368	Wednesday	09:00	17:00
John	Louise	25368	Thursday	09:00	17:00
David	Louise	69985	Friday	09:00	17:00
David	Louise	69985	Saturday	09:00	17:00
David	Zara	45896	Sunday	09:00	17:00
David	Zara	45896	Monday	09:00	17:00
David	Alex	65471	Tuesday	09:00	17:00
Steve	Alex	23698	Wednesday	09:00	17:00
Steve	Zara	23698	Thursday	09:00	17:00
Mark	Louise	44589	Friday	09:00	17:00
Mark	Alex	44779	Saturday	09:00	17:00
Mark	Louise	44589	Sunday	09:00	17:00
Martin	Louise	14589	Monday	09:00	17:00
Martin	Louise	14589	Tuesday	09:00	17:00
Martin	Zara	97879	Wednesday	09:00	17:00
Martin	Alex	45897	Thursday	09:00	17:00
Martin	Alex	45897	Friday	09:00	17:00
John	Zara	65896	Saturday	09:00	17:00
John	Zara	65896	Sunday	09:00	17:00
John	Louise	25368	Monday	09:00	17:00
John	Alex	99866	Tuesday	09:00	17:00
Ralph	Zara	87898	Wednesday	09:00	17:00
Ralph	Zara	64796	Thursday	09:00	17:00

<tbody>
</tbody>

Thank you very much!!ray:

 

[Aladin Akyurek]

G1: Zara (a manager of interest)

H1, control+shift+enter, not just enter:

=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]

G1: Zara (a manager of interest)

H1, control+shift+enter, not just enter:

=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]

Thank you very much!!!

You are welcome.

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

See:
http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949
http://www.mrexcel.com/forum/excel-...ting-summing-based-2-columns.html#post3000815