Count unique values among duplicates in a subtotal range
 

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!

Count unique values among duplicates in a subtotal range
 

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:"&ROW S(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" wrote in message
...
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!

Count unique values among duplicates in a subtotal range
 

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

"Bob Phillips" wrote:

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:"&ROW S(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" wrote in message
...
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!