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!
|