software

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 quot;Project Aquot; 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!

To count unique PMs use

=SUMPRODUCT((A1:A200lt;gt;quot;quot;)/COUNTIF(A1:A200,A1:A200amp;quot;quot;))

Unique PMs for Project A

=SUM(--(FREQUENCY(IF(B1:B200=quot;Project
Aquot;,MATCH(A1:A200,A1:A200,0)),ROW(INDIRECT(quot;1:quot;amp;ROW S(A1:A200))))gt;0))

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

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;jcpotworquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I have a list of names of people associated with a list of projects. One
gt; person can manage more than one project so the same name can appear many
gt; times. I would like to be about to count the number of unique names in
this
gt; list. Now that catch is that I also have a column for region (ie: east
gt; coast, west coast, etc.) so I would like to be able to use the filters but
gt; the count should be able to adjust accordingly.
gt;
gt; Ie: My List
gt;
gt; Joe Project A EastCoast
gt; Joe Project B EastCoast
gt; Bob Project A WestCoast
gt; Steve Project C Central
gt;
gt; I would love to get a count above of 3 names, but then also be able to
gt; filter for quot;Project Aquot; and get 2 names for the count. i know this one is
gt; pushing the limits of excel but I was hoping someone could help me out.
gt;
gt; THANKS!
Thanks Bob.

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

Thanks again,
Joe

quot;Bob Phillipsquot; wrote:

gt; To count unique PMs use
gt;
gt; =SUMPRODUCT((A1:A200lt;gt;quot;quot;)/COUNTIF(A1:A200,A1:A200amp;quot;quot;))
gt;
gt; Unique PMs for Project A
gt;
gt; =SUM(--(FREQUENCY(IF(B1:B200=quot;Project
gt; Aquot;,MATCH(A1:A200,A1:A200,0)),ROW(INDIRECT(quot;1:quot;amp;ROW S(A1:A200))))gt;0))
gt;
gt; The latter is an array formula, so commit with Ctrl-Shift-Enter.
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;jcpotworquot; gt; wrote in message
gt; ...
gt; gt; Hello,
gt; gt;
gt; gt; I have a list of names of people associated with a list of projects. One
gt; gt; person can manage more than one project so the same name can appear many
gt; gt; times. I would like to be about to count the number of unique names in
gt; this
gt; gt; list. Now that catch is that I also have a column for region (ie: east
gt; gt; coast, west coast, etc.) so I would like to be able to use the filters but
gt; gt; the count should be able to adjust accordingly.
gt; gt;
gt; gt; Ie: My List
gt; gt;
gt; gt; Joe Project A EastCoast
gt; gt; Joe Project B EastCoast
gt; gt; Bob Project A WestCoast
gt; gt; Steve Project C Central
gt; gt;
gt; gt; I would love to get a count above of 3 names, but then also be able to
gt; gt; filter for quot;Project Aquot; and get 2 names for the count. i know this one is
gt; gt; pushing the limits of excel but I was hoping someone could help me out.
gt; gt;
gt; gt; THANKS!
gt;
gt;
gt;