Try this array formula** :

D1 = EE
D2 = ME
D3 = CE

Entered in E1:

=SUM(IF(FREQUENCY(IF(B$1:B$10=D1,MATCH(A$1:A$10,A$ 1:A$10,0)),ROW(A$1:A$10)-ROW(A1)+1),1))

Copy down to E3

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"jkiser" wrote in message
...
I have names in A1:A10 like this:
Ted, Ted, Ted, Mark, David, David, Bonnie, Bonnie, Bonnie, Fred

I have their department assignments in B1:B10 like this:
EE,EE,EE,ME,CE,CE,ME,ME,ME,EE

I am using the following array formula to count the total number of
employees:
{=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1 :A10,A1:A10,0))0,1))}

This yields a total of 5 employees.

But I'd like to modify the formula to count the number of employees by
department. In other words, I'd like the formula to return that I have 2
employees in ME, 2 in EE and 1 in CE.

Thanks in advance for the help.