Counting with Multiples by sub group

[tourless]

Hi Folks.

I have column B with names, I have column E with department numbers. I need a way to count unique names grouped by department. I can figure out the counting unique values but can't work out the 'by department' part.

John	01
John	01
John	01
Mary	01
Mary	01	Dept 1: 2 names
Jim	02
Jim	02
Jim	02
Bob	02
Bob	02
Kevin	02
Kevin	02	Dept 2: 3names

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1		Name			Dept
2		John			1	2
3		John			1	2
4		John			1	2
5		Mary			1	2
6		Mary			1	2
7		Jim			2	3
8		Jim			2	3
9		Jim			2	3
10		Bob			2	3
11		Bob			2	3
12		Kevin			2	3
13		Kevin			2	3
Data

Cell Formulas
Range	Formula
F2:F13	F2	=ROWS(UNIQUE(FILTER($B$2:$B$100,$E$2:$E$100=E2)))

 

[tourless]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1		Name			Dept
2		John			1	2
3		John			1	2
4		John			1	2
5		Mary			1	2
6		Mary			1	2
7		Jim			2	3
8		Jim			2	3
9		Jim			2	3
10		Bob			2	3
11		Bob			2	3
12		Kevin			2	3
13		Kevin			2	3
Data

Cell Formulas
Range	Formula
F2:F13	F2	=ROWS(UNIQUE(FILTER($B$2:$B$100,$E$2:$E$100=E2)))

That might work but when I try to apply your formula I receive an that states the functions isn't valid.

 

[Fluff]

In that case your version of Excel is well out of date. The Filter function was released to all 365 over a year ago.

 

[Fluff]

Try

=SUM(--(FREQUENCY(IF($E$2:$E$100=E2,MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW($B$2:$B$100)-ROW($B$2)+1)>0))

You will probably need to confirm this with Ctrl Shift Enter

 

[tourless]

In that case your version of Excel is well out of date. The Filter function was released to all 365 over a year ago.

2016 Pro Plus

 

[Fluff]

In that case I would suggest that you update your profile to show 2016, rather than 365.