# Count Employees per Manager without duplicates

#### juanma2550

##### New Member
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.

 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:

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

##### MrExcel MVP
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
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