Count unique values among duplicates in a subtotal range

[jcpotwor]

Hello,

I have a list of names of people associated with a list of projects. One
person can manage more than one project so the same name can appear many
times. I would like to be about to count the number of unique names in this
list. Now that catch is that I also have a column for region (ie: east
coast, west coast, etc.) so I would like to be able to use the filters but
the count should be able to adjust accordingly.

Ie: My List

Joe Project A EastCoast
Joe Project B EastCoast
Bob Project A WestCoast
Steve Project C Central

I would love to get a count above of 3 names, but then also be able to
filter for "Project A" and get 2 names for the count. i know this one is
pushing the limits of excel but I was hoping someone could help me out.

THANKS!

 

[Bob Phillips]

To count unique PMs use

=SUMPRODUCT((A1:A200<>"")/COUNTIF(A1:A200,A1:A200&""))

Unique PMs for Project A

=SUM(--(FREQUENCY(IF(B1:B200="Project
A",MATCH(A1:A200,A1:A200,0)),ROW(INDIRECT("1:"&ROWS(A1:A200))))>0))

The latter is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[jcpotwor]

Thanks Bob.

Will this work if the list has an autofilter on it and if I filtered by
"Project A" the sum of PMs would adjust accordingly?

Thanks again,
Joe